.EntireRow.Select question

[Radu]

Hi.

In the main sheet I have the list of clients. When I select a row (a
client), a popup screen appears (for this, I'm using the
SelectionChange event), containg N records for that specific client,
these records being the rates. When the user selects a rate (a row) in
this popup screen, I need to populate some text/combo boxes with the
details of that specific rate.

The rates in this popup screen are in a spreadsheet control (Microsoft
Office Components). The problem is this one:

In the UserForm_Activate event I call
listRates.Selection.EntireRow.Select

This calls listRates_SelectionChange. Here I need to select THE WHOLE
row. This event would be also triggered, of course, when the user
clicks on a specific cell in the spreadsheet - the row of that cell
gets selected.

So I say:
listRates.ActiveCell.EntireRow.Select
It works quite nicely, but the problem is that after the selection, I
am looking at the last column in the grid, which, of course, is empty -
I only have 12 columns with data, A thru L, not 720, the total number
of columns. That means that the user should scroll back 720-12 columns.
Not nice.

I have also tried

listRates.ActiveCell.CurrentRegion.Rows(listRates.ActiveCell.CurrentRegion.Row).Select

and other variants, with the same result.

If I say
listRates.ViewableRange = "A1:L20"

then there's no need for scrolling horizontally, but the line
listRates.Selection.EntireRow.Select
fails saying that not the whole row is into the viewable range.

Please help, I'm really stressed out by this back-scrolling.

Thanks a lot, Alex.

[quartz]

Radu,

I'm not sure I follow your illustration completely, so I won't attempt to
supply an exact solution, but take a look at the following two methods for
scrolling using VBA. Someone else more skilled than I may post a better
answer. But, you should be able to adapt one of these to your situation:

Method 1: ActiveWindow.ScrollRow = lngRow - 20 'note: lngRow is a variable

Method 2: Application.Goto Range("A21"), Scroll = True

HTH.

"Radu" wrote:

> Hi.
>
> In the main sheet I have the list of clients. When I select a row (a
> client), a popup screen appears (for this, I'm using the
> SelectionChange event), containg N records for that specific client,
> these records being the rates. When the user selects a rate (a row) in
> this popup screen, I need to populate some text/combo boxes with the
> details of that specific rate.
>
> The rates in this popup screen are in a spreadsheet control (Microsoft
> Office Components). The problem is this one:
>
> In the UserForm_Activate event I call
> listRates.Selection.EntireRow.Select
>
> This calls listRates_SelectionChange. Here I need to select THE WHOLE
> row. This event would be also triggered, of course, when the user
> clicks on a specific cell in the spreadsheet - the row of that cell
> gets selected.
>
> So I say:
> listRates.ActiveCell.EntireRow.Select
> It works quite nicely, but the problem is that after the selection, I
> am looking at the last column in the grid, which, of course, is empty -
> I only have 12 columns with data, A thru L, not 720, the total number
> of columns. That means that the user should scroll back 720-12 columns.
> Not nice.
>
> I have also tried
>
> listRates.ActiveCell.CurrentRegion.Rows(listRates.ActiveCell.CurrentRegion.Row).Select
>
> and other variants, with the same result.
>
> If I say
> listRates.ViewableRange = "A1:L20"
>
> then there's no need for scrolling horizontally, but the line
> listRates.Selection.EntireRow.Select
> fails saying that not the whole row is into the viewable range.
>
> Please help, I'm really stressed out by this back-scrolling.
>
> Thanks a lot, Alex.
>
>