Detecting Selected WorkSheet

[LeoNgan]

I would like to write a macro by depending on the user SELECTED
worksheet to work on something.

Is it possible to detect (know) which page or pages are selected by the
user? please

Thank a lot!!

 

[pfsardella]

strWs = ActiveSheet.Name

Select Case strWs
Case "Red": ' Do this
Case "Blue": ' Do that
Case "Yellow":' Do the other.
Case Else: ' Do something else.
End Select

HTH
Paul

 

[Serkan]

Private Sub Worksheet_Activate()
x = ActiveSheet.Name
i = 2
Do
If Sheets("Summary").Cells(i, 1) = "" Then
Sheets("Summary").Cells(i, 1) = Application.UserName
Sheets("Summary").Cells(i, 2) = x
Sheets("Summary").Cells(i, 3) = Now()
GoTo ext
Else
i = i + 1
End If
Loop
ext:
End Sub


you create a sheet, name= Summary
A column User name
B column sheet name which is using by user
C column Date

 

[Serkan]

write this code in all worksheet

-----Original Message-----
Private Sub Worksheet_Activate()
x = ActiveSheet.Name
i = 2
Do
If Sheets("Summary").Cells(i, 1) = "" Then
Sheets("Summary").Cells(i, 1) = Application.UserName
Sheets("Summary").Cells(i, 2) = x
Sheets("Summary").Cells(i, 3) = Now()
GoTo ext
Else
i = i + 1
End If
Loop
ext:
End Sub


you create a sheet, name= Summary
A column User name
B column sheet name which is using by user
C column Date

-----Original Message-----
I would like to write a macro by depending on the user SELECTED
worksheet to work on something.

Is it possible to detect (know) which page or pages are selected by the
user? please

Thank a lot!!



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.

.

 

[LeoNgan]

No .. you mis-understand my meaning ..

in Excel you can select multiple worksheet by click the tab with shift
button (Sheet1, Sheet2, Sheet3, etc) and then apply function on them.

Now I would like to write a macro, which effect to the user's selected
worksheet. It is a dynamical situation.

Got it ??

 

[Tom Ogilvy]

VBA doesn't really support what you describe. Some of this functionality
can be achieved by using the selection object, but it is more robust to loop
through the sheets and make the change on each sheet

for each sh in ActiveWindow.Selectedsheets
' add code to do the work such as:
sh.Range("A1").Interior.ColorIndex = 3
Next

 

[LeoNgan]

No .. you mis-understand my meaning ..

in Excel you can select multiple worksheet by click the tab with shift
button (Sheet1, Sheet2, Sheet3, etc) and then apply function on them.

Now I would like to write a macro, which effect to the user's selected
worksheet. It is a dynamical situation.

Got it ??

 

[Tom Ogilvy]

No, you mis-understand. Excel VBA doesn't support this. I described a
workaround.

Got-it

 

[Tom Ogilvy]

Anyway, all kidding aside, the problem you describe can be done using the
selection object as I originally stated:

Sub Tester2()
Dim shts As Sheets
Dim sh As Worksheet
Set shts = Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
Set sh = ActiveSheet
shts.Select
Range("A1").Select
Selection.Formula = "=Sum(C1:C30)"
sh.Select

End Sub

but also, in general, grouping sheets in vba (as above) is not supported.

--
Regards,
Tom Ogilvy

No, you mis-understand. Excel VBA doesn't support this. I described a
workaround.

Got-it