Excel VBA 'Jump To' Code

[RichardBisset]

I have two worksheets. On one I have a sheet which displays a form (consisting of VB controls). On the other sheet i have a list of records.

Sheet one displays each record on sheet 2 one at a time and has navigation buttons to move to next record etc...

I need to be able to click on a record on sheet 2 which will then jump the user to the form sheet which will display the details for that record.

Ideally I would create a button array on sheet 2 which create a button for each row on the sheet, but VBA Excel doesnt seem to support control arrays

alternatively i was thinking i could get started by just having the user select a cell in sheet 2, then manually click on the sheet 1 tab and then sheet 1 would have a function to detect the currently selected Cell on sheet 2 - IS THIS POSSIBLE

Thanks, any help will be appreciated.

 

[RobBroekhuis]

You can do this in a few ways. Certainly the second way you suggest is possible - in the worksheet_activate event for sheet 1, put something like

sheets(2).activate
set cell=selection
sheets(1).activate

Now you can use the range object "cell" to do whatever you need to populate the form.

Rob

 

[RobBroekhuis]

I forgot to go on to suggest an alternative: use the

worksheet_selectionchange event handler on sheet 2. Designate column A as the selection trigger (perhaps put images of checkboxes or buttons in column A, without a real object).

sub worksheet_selectionchange
if target.column=1 then
sheets(1).activate
...use target range object to populate form here
end if
end sub
Rob

 

[RichardBisset]

Thanks for your help RobBroekhuis. That has solved my problem really. I dont think the button jump to feature was feasible.

one last query i have though is whether it is possible to select a row on another sheet without activating the sheet first. I want to traverse through records on sheet1 and select the appropriate row on sheet2 with out displaying sheet2. so when the user tabs back to sheet 2 it will have the appropriate row selected:

'ON RECORD CHANGE IN SHEET 1
sheet2.activate
sheet2.rows(newRecordNo).select
sheet1.activate

the above causes excel to flash between two sheets which doesnt look very neat. Is there a kind of DontDoEvents function i can use!!

 

[xlbo]

You could use
application.screenupdating = false
code
application.screenupdating = true

This generally stops any flickering Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde