Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I know which worksheet I chose using VBA for excel 1

Status
Not open for further replies.

cathy68

Programmer
Aug 6, 2003
23
NZ
Hi Guru
I created one sheet in my excel file, and I created a customized bar as “NEW BAR”. In the customized bar, there are 5 buttons. One of them is called “ScrollRight1Screens”, underneath function as:

Sub ScrollRight1Screens()
Worksheets("sheet1").Activate
ActiveWindow.SmallScroll ToRight:=10
End Sub

Now I create another sheet, called “sheet2”. What I want is when I choose “sheet1”, using “ScrollRight1Screens” button, sheet1 is scrolled right. When I choose “ sheet2”, using the same button and same function. My question is how can I know which sheet I chose (or which one is activate) using VBA for the same function? I don’t want when I click the button, both of worksheet moving, which I achieved before:

Sub ScrollRight1Screens()
Worksheets("sheet1").Activate
ActiveWindow.SmallScroll ToRight:=10
Worksheets("sheet2").Activate
ActiveWindow.SmallScroll ToRight:=10
End Sub

If anyone can help me, I am very appreciated.

Cathy
 
Have you tried simply
Code:
  Sub ScrollRight1Screens()
    ActiveWindow.SmallScroll ToRight:=10
  End Sub
In other words, don't select any screen in code if you want to process the active sheet.

 
Hi Zathras
I meet another problem. same file, same customized bar, another button call"GoToday", the previous code is:
Sub GoToday()
cellname = ConvertCellNotation(1, 1)
Worksheets("sheet1").Range(cellname).Activate
Fcol = 1
Do Until CDate(Worksheets("sheet1").Cells(2, Fcol)) = Date
Fcol = Fcol + 1
Loop
cellname = ConvertCellNotation(2, Fcol)
Worksheets("sheet1").Range(cellname).Activate
ActiveWindow.SmallScroll ToRight:=6
Application.DisplayFullScreen = True
End Sub

the function is When I find today's date in sheet1, display it in left side position and Full screen. but now I add sheet2, I want to implement same function, the problem is how can I make the today's cell in sheet2 to be activate, and set up full screen?
Don't worry about "ConvertCellNotation()", its function as convert "cell(1,2)" to "cell(B1)"

Many thanks in advance.
 
You should start a new question in a new post.

But here are a couple of tips you might consider to make your life easier.

1. Instead of your "ConvertCellNotation" business, you can more simply use the Cells property of the worksheet. I.e. to select cell "B1" by row and column numbers all you need is
[blue]
Code:
  Cells(1,2).Select
[/color]

2. To select the cell in row 2 that has today's date all you need is
[blue]
Code:
  Range("2:2").Find(Date).Select
[/color]

3. To perform the steps on the active sheet, remove your references to "sheet1" as I indicated earlier.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top