Button caption on Workbook_Open() how to change?

Radar

New Member
Joined
Dec 7, 2005
Messages
4
Howdy gang. Great forum. I've searched, tried several ideas (code), but have not found the one that works yet.

Ref: Excel 2000 workbook used by several users. For sheet1 only, one user wants the zoom set to 87%, the others want the zoom at 100%. In order to make it a little easier for them I have created a (control toolbox) command button that switches (toggles) between the two zoom levels.

Private Sub cmdZoom_Click()
If cmdZoom.Caption = "87%" Then
cmdZoom.Caption = "100%"
ActiveWindow.Zoom = 87
ElseIf cmdZoom.Caption = "100%" Then
cmdZoom.Caption = "87%"
ActiveWindow.Zoom = 100
End If
End Sub

The worksheet is set to always open at 87% zoom:

Private Sub Workbook_Open()
ActiveWindow.Zoom = 87
End Sub

Question:
Upon Workbook_Open, how do I ensure (change) the button caption so it is always "100%"? This way the first click will change the zoom to 100%.

I know I am close, but seem to be dancing around just the correct code.
Thanks, Ross
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Welcome to MrExcel Board!

Have you tried doing something like:

Code:
    Select Case Environ("username")
    Case "user1"
        ActiveWindow.Zoom = 87
    Case "user2"
        ActiveWindow.Zoom = 75
    Case Else
        ActiveWindow.Zoom = 100
    End Select

in thw Workbook_Open() event? Hope that helps! Post back if you have problems getting this to work.
 

Radar

New Member
Joined
Dec 7, 2005
Messages
4
Taz,
Tried it. Copied it exactly except I substituted the actual user name as found in Excel Tools|Options|General| User name. In the quotes for each case I put the user name. Was this correct?
It did not work. For two users (3 cases), one user at 100% zoom, the other user at 87% zoom, and the Else Case at 150% as a verification. Any ideas what I missed?

If we can get this to work it is a much more elegant solution then my original idea.

Thanks.

~5 minutes later~

Okay, funny how things work out.

As any good computer engineer would (should) do, we organize our thoughts as we are working through the problem, and before presenting it to someone else for help. So, as I was proof reading my reply I had an epiphany. Taz is talking about the actual system (computer) user logon username, not the Excel user name.

Ba-Bing it works like a charm. I also find (as I think most do) solutions on one problem end up working in many other projects. This one I will catalog away for future use. Yup, one is always learning...

Way Cool, Thanks,
Ross
 

Radar

New Member
Joined
Dec 7, 2005
Messages
4
Taz,
Thanks for the solution on this problem.

But, is it possible to solve it as I had tried originally? Specifically, use the Workbook_Open() subroutine to name (change) a button's caption?
I have tried it as:

Private Sub Workbook_Open()
cmdZoom.Caption = "100%"
End Sub

But receive the error:
Run-time error '424':
Object required

Any ideas?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,455
Messages
5,572,216
Members
412,448
Latest member
ManuW
Top