cells not changing selection after sheets array
 

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

--
Message posted from http://www.ExcelForum.com

cells not changing selection after sheets array
 

You can only select a cell on the active sheet.

--
Regards,
Tom Ogilvy

"Simon Lloyd " wrote in message
...
Hi Guys!

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


---
Message posted from http://www.ExcelForum.com/

cells not changing selection after sheets array
 

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

--
Message posted from http://www.ExcelForum.com

cells not changing selection after sheets array
 

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


--
Regards,
Tom Ogilvy


"Simon Lloyd " wrote in message
...
Hi Tom,

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

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


---
Message posted from http://www.ExcelForum.com/

cells not changing selection after sheets array
 

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

--
Message posted from http://www.ExcelForum.com