Detecting Selected WorkSheet

  • Thread starter Thread starter LeoNgan
  • Start date Start date
L

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!!
 
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
 
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
 
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


.
Click to expand...
 
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 ??

:)
 
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
 
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 ??

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

Got-it
 
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

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

Got-it
Click to expand...
 
Back
Top