Place a Toggle Button Automatically on a UserForm

Hi Siamadu

Mayby this?

Enter this code in wokseet module (right click on worksheet tub, select View Code)

Please Login or Register  to view this content.

You should have control ToggleButton1 (Visible = False) on user form and user form’s “ShowModal” property must have value “False”

place this code in a Standard module

Please Login or Register  to view this content.

Then in the worksheet change event

Please Login or Register  to view this content.

Add Code to a Regular Module
Copy the code
Select the workbook in which you want to use the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

Copy Code to a Worksheet Module
Copy the code
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

Wow, nice work, Roy

Methinks he wants to add it to an existing form below other like buttons.

Thanks Shg, perchance with a UserForm that has ShowModal set to False.

Please Login or Register  to view this content.

You need a reference. An easy way to get it is to insert a form, and then delete it.

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Unsubscribing ...

Post the link if you want further help

Hi Siamadu,

Please see file attached. It have tested it works.

Sahak,

This thread cannot continue until siamadu follows the rules. Once the link is added you're thread will be restored

VBA Noob

siamadu,

Noob is talking about posting links to posts on other forums (codecage, mrexcel, etc) where you have posted the same question.

If you click on the link in Noob's signature, A message for cross posters, it will all become clear to you. It is basic etiquette in any forum.

Please find a link I posted about my original post:

http://www.mrexcel.com/forum/showthread.php?t=370779

It would help if we knew
Which code you are using?
Why ToggleButtons?


The code that i gave you created ToggleButtons dynamically, which is what you asked for. The userform posted has hidden togglebutton{s}, this would be simplest if you wanted to run code from them.

To get a proper answer you should state clearly what you intend doing, this , along with cross posting, is covered in the Forum Rules.

As for the toggle button code, I receive two different examples, one from you and the other from Sahak. I think my confusion with you code, is that I did not know what to do with the last code you provided me. I apologize for not getting back to you with my questions.

Since the Forum Rules were brought to my attention, I went and added all of my links to the threads I create here and elsewhere. You should find it above.

I think I just need some clarity of how to input you second code you posted

Please Login or Register  to view this content.

You gave me perfect instructions on how to handle the first code, but I was not sure how to input this code in VBE
Should this code be replacing any of the first code?
Per shg response, will I need to already have an existing userform1 created before I copy/paste your code?
As for now, when I copy the first code I get this error message: Run-time Error '1004' - Programmatic access to Visual Basic Project is not trusted

And the debug points to this line of code

Please Login or Register  to view this content.

I have attached a copy of the excel workbook where I have inputted the code.

Hello siamadu,

The problem was your button wasn't calling the macro "AddForm". I made the change for you in the attached workbook.

The instructions for using the code were the same for both examples. It replaces the first set of code and is written to add a togglebutton o an existing userform.

To allow access to the project

Office 2003 and Office XP
Open the Office 2003 or Office XP application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.
On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access.
Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.

Office 2007
Open the 2007 Microsoft Office system application in question. Click the Microsoft Office button, and then click Application Options.
Click the Trust Center tab, and then click Trust Center Settings.
Click the Macro Settings tab, click to select the Trust access to the VBA project object model check box, and then click OK.
Click OK.

You still haven't said why you ar usin a togglebutton, nor what the purpose o the code is.

Hi Siamadu,

File sent by Leith Ross works perfect (I have tested). I think you didn’t do what told you to do RoyUk about Trust access to Visual Basic Project.

RoyUK, Sahak, and Leith,

Thank you all very much. I apologize for my ignorance. I am using different compute which as Office 2007. I am the necessary changes that RoyUK give me and first set of code runs fine. What I did was I opened excel spreadhsheet with the change that Leith made and put in "Test" in cell A1 and hit enter. The userform automatically appeared.

Now, I tried to revised the code to include cell A2 to see if it would also had another toggle button and it did not. I believe that I do not make the correct revisios in the proper place. Will the initial code handle more than one toggle button? For example, entering data in Range("A1:A6")

Please Login or Register  to view this content.

And the second part of the code I used

Please Login or Register  to view this content.

To answer RoyUK's question about why toggle buttons. I have users who are using an existing report with toggle buttons. They are not what you will call frequent excel users. They are not people who are really receptive to change. Just to get them to use excel was like pulling teeth. When they were able to see how the toggle buttons work along with there what-if scnerios, it was a win win.

So, tyring to get them to use anything else now that we have got use to using the toggle buttons, will be a big set back for me. Any other excel reports I haveed and want to create in the future will be looked at as having not intergrity.

Trying to teach an old dog new tricks can be challanging when they are not technical people.

That is the reason for using toggle buttons, RoyUK. Please understand that I was not tyring to down playing your suggestions.

Hello siamadu,

I have added this macro to the attached workbook. The range is set to "A1:A10" on "Sheet1". You can change this later if you need to. It is marked in red.

Please Login or Register  to view this content.

I removed the following code from the workbook as it is not needed.

Please Login or Register  to view this content.