cells not changing selection after sheets array

S

Simon Lloyd

Hi Guys!

I have a macro that selects all sheets in an array, and then inputs th
value from a user form in the selected cell, but when i select anothe
cell on another sheet the activecell reverts to the last on
used.....any ideas on how to cure this
 
S

Simon Lloyd

Hi Tom,

Thanks for the reply, i want to be able to select a cell on any shee
and when i do (within a range of couurse) i want all sheets to b
selected but when they are all selected the initial cell i chose need
to be the active one even if it appears on the activ
sheet....something like......choose cell f34 on sheet3 (of say 5....
have more than this) then it should select all sheets and the activ
cell should stay F34 but when all are selected the last active cell o
the sheet in view is selected???

I have a very short version of the workbook (only 3 sheets) that i ca
send you if you want to see the problem....if you choose the firs
sheet make a selection in the range I3:H200 a userform appears make an
selection you like......now choose sheet2 and click a cell in the sam
range....all sheets select but you will now find the cursor has jumpe
to the cell you first used on sheet1...i cant figure it out
 
T

Tom Ogilvy

As I said, the selected cell must be on the activesheet.

If you select all sheets, then you would have to rearrange the order of your
array so the sheet you want as the activesheet is the first element.

for example, if I want to use the activecell on sheet3 as the activecell for
all the sheets in the group and I want to group all sheets, then I would do
this: (6 sheets in the workbook).

Sub AATester2()
Dim Varr as Variant
varr = Array("Sheet3", "Sheet2", "Sheet4", _
"Sheet1", "Sheet5", "Sheet6")
Worksheets(varr).Select
End Sub

If I have at least one sheet not selected, then I can use an approach like
this: (one of the sheets that will be grouped must be selected when you run
this).

Sub AAtester1()
Dim cell as Range, varr as Variant
Set cell = ActiveCell
varr = Array("Sheet1", "Sheet2", "Sheet3", _
"Sheet4", "Sheet5")
Worksheets(varr).Select
Application.Goto cell
End Sub
 
S

Simon Lloyd

Tom,

Prior to using a userform i used a listbox, this worked fine when
used validation, allow list......when i selected a cell i would get
listbox dropdown list and the macro on each sheet was to select al
sheets....this worked fine and your selection would go where you put i
and would be in the same place across all sheets...but now i have go
the program looking smarter using a userform it doesnt work right...
have mailed you a short rip off from my 21 sheet workbook, as it i
780k which is too big for this forum.

Simo
 
Top