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

[cathy68]

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&quot.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&quot.Activate
ActiveWindow.SmallScroll ToRight:=10
Worksheets("sheet2&quot.Activate
ActiveWindow.SmallScroll ToRight:=10
End Sub

If anyone can help me, I am very appreciated.

Cathy

 

[Zathras]

Have you tried simply

  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.

 

[cathy68]

Thank you Zathras!!!!

 

[cathy68]

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&quot.Range(cellname).Activate
Fcol = 1
Do Until CDate(Worksheets("sheet1&quot.Cells(2, Fcol)) = Date
Fcol = Fcol + 1
Loop
cellname = ConvertCellNotation(2, Fcol)
Worksheets("sheet1&quot.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.

 

[Zathras]

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]

  Cells(1,2).Select

[/color]
2. To select the cell in row 2 that has today's date all you need is
[blue]

  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.

 

[cathy68]

thanks Zathras, you are really star