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]

Is this what you want.

Your correct there is not Right and Bottom

Private Sub CommandButton1_Click()
'Modified  6/1/2019  2:48:29 AM  EDT
With ActiveSheet.ToggleButton1
.Left = ActiveCell.Left
.Top = ActiveCell.Top
.Width = ActiveCell.Width
.Height = ActiveCell.Height
End With
End Sub

 

[My Aswer Is This]

Just curious:

How many buttons are we talking about?
Is this a problem with two or three or more.

Is there a standard location where you want them?

Like straight down a column Like Column(1)

And what are these Toggle Buttons being used for?

 

[sassriverrat]

excellent. Thank you again sir. That was perfect

Oh- to answer your question- I have 3 toggle buttons that are in oddball places, otherwise yes, about 18 toggle buttons in a single column and another 6 in another column.

they are coded to change some cell values and I have their captions set to change with some code I put together.

 

[My Aswer Is This]

Again glad I was able to help you.

Not sure how much you know about Vba but there are other ways to run code that does not require Buttons

If your interested you can let me know.

Like you can Double click on a cell to run a script

You can for example Double Click on Range("A1") to enter "Hello" into Range("G14"

Just Double Clicking on a cell can run any script.

Having a large number of buttons on a sheet can be a Pain.

But then some people like seeing Buttons on their sheet.

 

[sassriverrat]

certainly no expert but I can dabble.

So I have a massive workbook that, if I could only delete the excel menu and the cell input/formula box along the top, would look like it's own program running. I use a TON of userforms, have cell generating sheets, etc. All fairly automated and runs like a champ. There's a "Settings" sheet that has the toggle buttons for turning "on/off" features (turns on and off other macros that run in other sheets) and I have, among other things, some buttons that do other things. Another example: I have a button that generates an "Archives" folder in a locally-specified folder while a second button generates (on user demand) an "Archives" Folder on a network-specified address. then the report that is automatically created and printed daily also is automatically emailed to certain individuals (via toggle button on/off). Anyway, the list goes on but it's a massive file. Now if I could only learn how to hide the whole excel menu and such so it was just a sheet.....it would look awesome!


Thanks

 

[My Aswer Is This]

If you want to hide the Formula Bar and the ribbon:
Try this:

Application.DisplayFormulaBar = False
Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", False)"

 

[My Aswer Is This]

Wow:
Sounds like you know Vba very well. A great amount of code needed to do a project this big.

Glad to see you know Excel very well.

 

[My Aswer Is This]

You said I have a Ton of UserForms.

Have you ever used a UserForm Multipage?

It's like having 10 UserForms on the same UserForm

Just like have 10 sheets on the same Workbook.

 

[sassriverrat]

You said I have a Ton of UserForms.

Have you ever used a UserForm Multipage?

It's like having 10 UserForms on the same UserForm

Just like have 10 sheets on the same Workbook.

Oooh I'll have to play with that bit of ribbon code. I haven't heard of that before! I'm excited.

As for a userform multipage, no I haven't I'd be interested to learn more- My way is probably not at all the most efficient because I'm using "Next" and "Previous" buttons which are just buttons with Userform#.Show and Unload me....ya know.

I was surprised- i had a function kick an error today when I opened up another workbook- first time that's happened. And then when I removed the "On Error Goto" piece it didn't do it anymore...never seen that before.

 

[sassriverrat]

If you want to hide the Formula Bar and the ribbon:
Try this:

Application.DisplayFormulaBar = False
Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", False)"

I like I like!

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).

Also- this is awesome! Thank you.

Side note- Is it possible to make it so that it ONLY happens to this workbook (if i had another workbook open it isn't the same on that one)?

Thank you again!