Button caption on Workbook_Open() how to change?

[Radar]

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

 

[Tazguy37]

Welcome to MrExcel Board!

Have you tried doing something like:

    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]

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]

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?