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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,002
Messages
5,834,821
Members
430,324
Latest member
bosphoruskid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top