toggle button size modified via vba

[sassriverrat]

I'm 99% you can't make a toggle button using code, so can you change the size and location of one via code?

I have a few on a sheet and they seem to have resized/relocated on their own when switch computers and it's quite annoying so I was just going to have code (if possible) to ensure they don't do that again.

Thanks!

 

[My Aswer Is This]

Glad I was able to help with Hiding Menu Bars
I could provide code so you can toggle them shown and hidden if you wanted.

As far a Multipage.



I would create a New UserForm and Then Place a Multipage in the center of the UserForm But leave a little space at the bottom of the UserForm outside the Multipage

Now you will see the Multipage looks sort of like a Workbook with Tabs on the top
Click on the tabs and you see different Multipage Pages

And you can add new pages by just right clicking on a Multipage Page while in Vba Mode

Now you can add all the controls you want to each Multipage Page

And you can add controls to the Userform that you want to see all the time on the UserForm outside the Multipage

A button for example that closes the UserForm you would want to see all the time so put that button outside the Multipage

If your writing code to do certain things remember the first Multipage Page is Page 0 The second Multipage Page is Page 1


You should only need one Multipage With a lot of Multipage Pages

Think of Multipage1 as a Workbook. And the Pages as Worksheets

 

[sassriverrat]

Coding worked that you gave me- worked like a champ.

Regarding multipages because now I'm intrigued-

So I took a screenshot here (zoomed out a little so it looks better a little closer in) of a page. The page is entirely generated via VBA (formulas, sheet format, buttons, etc). The user inputs into the yellow boxes. Additionally, by clicking the bottom two buttons, other boxes become visible (formatting changes) and can be hidden again when not in use...

Anyway- you're saying all of this could go into a "tab" within a multipage so the entire workbook ran like an individual program instead of a complex excel sheet?

That would be AMAZING!

I'd love to see a little example code if you have the time and anything available?

Thanks again

 

[My Aswer Is This]

You asked:

Is there a way to specify the window size (I want to make sure the user always sees each sheet to the max (versus scrolling left/right) and yes, the sheet isn't so large that on a lower res monitor it wouldn't work).

Not sure what your wanting.

Excel has 15,000 columns how would you expect to see all 15,000 columns without scrolling ?

Or do you have just a certain Range you want to see?

Like maybe columns H to P


As far a hiding the menu bars when I test it it only effects the active workbook.

If I open another workbook it does not effect that other workbook

​

 

[My Aswer Is This]

We may be getting ahead of ourself

You said code worked perfectly. I assume you meant code to hide menu Bars.

Then you said:

The page is entirely generated via VBA (formulas, sheet format, buttons, etc).

Wow. You have code that is generating all the buttons and other controls.

If that is the case your way more advanced with vba then me.

You said you have a Ton of Userforms and your saying you created all these UserForms using code to place all the buttons and then code that tells the buttons what to do.

That would be a lot of coding for sure.

I should be asking you questions.
​

 

[sassriverrat]

no no I'm just an amateur with a lot of time on my hands and someone that likes a challenge (and math!)

Yes, your code worked to hide the menu bars and such. Further, I just changed it to show the menu bars and formula bars when the workbook closed.

It did affect the activeworkbook, just as you said. I was wondering- sometimes someone might reference another workbook when inputting data into mine- so I was wondering if it was possible to have the menu bar and such only hidden in my workbook and showing in the other (and the other might be the activeworkbook at times....). i just had an idea on this but not sure if it would work...

something like

If workbook.name = "Master Voyage Report" then
Application.DisplayFormulaBar = False
Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", False)"

Else: Application.DisplayFormulaBar = True
Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", True)"

End If

Not sure where I would put this though because it's gotta be every time the activeworkbook changed.

 

[sassriverrat]

Oh- and to show columns A:AA and Rows 1-108 on any given sheet to restrict the window size. As I said, the more the workbook looks like an independent program and not an excel sheet the more I'm wow'd!

 

[My Aswer Is This]

I'm sorry I do not know how to do what your asking for.
You want to be able to view 27 columns without using a scroll bar.

And I'm not sure what your attempting to do about Hiding menu bars on the active workbook and none of the others. Or maybe it's the other way around and the active workbook will not always be the same.

I assume you must be having several users accessing your Workbook all at the same time and you want some but not all to see the Menus.

Maybe someone else here on Mr. Excel will have a answer.

 

[sassriverrat]

for the second part:

No no. one user viewing the workbook at a time. but if that user as multiple workbooks open, I'd love for only the workbook named "xxx" to have the menu bars hidden- all other workbooks have it visible- so if the user is swapping between workbooks then the menu bar is constantly switching. Does that make sense?

 

[My Aswer Is This]

So do these users have Multi screen computers?

If not then if you always want the active workbook to have hidden menus why do you care if the other non active workbook menus can be seen. If I cannot see the workbook why do I care if the menu bars are visible.

 

[sassriverrat]

No, one monitor but they are constantly flipping between open workbooks- and I want one specific workbook to have the hidden menus....

So if I have two workbooks open, and I am reading data from one (call it x) and then inputting data into the other (call it y) and I want the menus hidden only in the x workbook. Make sense?