Is is possible to automatically place a toggle button on a user form by entering data in a cell??
In otherwords, if I type a number in cell A1, can that number be automatically put on the toggle button and placed on active userform??
Is is possible to automatically place a toggle button on a user form by entering data in a cell??
In otherwords, if I type a number in cell A1, can that number be automatically put on the toggle button and placed on active userform??
Hi Siamadu
Mayby this?
Enter this code in wokseet module (right click on worksheet tub, select View Code)
You should have control ToggleButton1 (Visible = False) on user form and user form’s “ShowModal” property must have value “False”Please Login or Register to view this content.
Last edited by Sahak; 02-12-2009 at 03:15 PM.
place this code in a Standard module
Then in the worksheet change eventPlease Login or Register to view this content.
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
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Wow, nice work, Roy
Methinks he wants to add it to an existing form below other like buttons.
Entia non sunt multiplicanda sine necessitate
Thanks Shg, perchance with a UserForm that has ShowModal set to False.
Please Login or Register to view this content.
Thank you very much RoyUK. I have followed the instructions, but I seem to be getting a error:
Complier error: User define type not defined.
Please advise
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!
_________________________________________
Credo Elvem ipsum etian vivere
_________________________________________
A message for cross posters
Please remember to wrap code.
Forum Rules
Please add to your signature if you found this link helpful. Excel links !!!
Unsubscribing ...
Thank you Sahak. I have inputted the code. When I enter data on sheet2, A1( this is where I pasted the code), and I run the user form, nothing appears. All both of the togglebuttons are invisable. It is just a blank userform.
Any ideas?
Post the link if you want further help
Please forgive me. I am new to the forum. When you mean link, are you referring to my excel spreadsheet?
I have posted it if that is what you need. If it is not, let me know so I can correct myself.
Thanks for all your help.
Hi Siamadu,
Please see file attached. It have tested it works.
Last edited by Sahak; 02-12-2009 at 08:20 PM.
Sahak,
This thread cannot continue until siamadu follows the rules. Once the link is added you're thread will be restored
VBA Noob
http://www.excelforum.com/excel-prog...-userform.html
Here is the link.
siamadu,
Noob is talking about posting links to posts on other forums (codecage, mrexcel, etc) where you have posted the same question.
Oh!! I did not have a cluse as to what Noob was talking about. That went right over my head!!! I figure he was talking about internally. But RoyUK said to me post the link, did not know he even knew is asking for help else where.
I figured that people are busy and my not get to mine. There are several people posting questions that need help too. Therefore, I did not know it was illegal to ask other forums for the same help.
So I just go back to the other forums and post all of there links? What if they did not provide me with an answer? What if they did provide me with an answer? Should I post it any way?
I have never been on a forum in my life, so I do not know the rules.
Trust me, it was intentionally.
I want to personally thank you helping with the matter. I did not mean or want to pist anyone off.
Best regards,
siamadu
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
Last edited by Leith Ross; 02-12-2009 at 11:02 PM.
Sahak,
Thanks for the code. Tt worked!!! The only problem is that when I close the userform and reopen it, the toggle button disappears. Also, will this work for to enteries in Columns A. For example, A2 = togglebutton2, A3=togglebutton3, A4=togglebutton4, etc.
Should a loop be added? Just a thought
Hi Siamadu,
How many togglebuttons you need to be added on user form?
Too many??????
The toggle buttons should probably be a maxium of 20.
Siamady,
I don’t know what are you trying to do with 20 toggle buttons but I could suggest to do same thing with one toggle button. Select or double click the cell you need (A1 or A2 or A55) and toggle button will take that cell's value. This is my opinion.
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
You gave me perfect instructions on how to handle the first code, but I was not sure how to input this code in VBEPlease Login or Register to view this content.
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
I have attached a copy of the excel workbook where I have inputted the code.Please Login or Register to view this content.
Last edited by siamadu; 02-14-2009 at 07:16 PM.
Hello siamadu,
The problem was your button wasn't calling the macro "AddForm". I made the change for you in the attached workbook.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star below the post.3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
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.
Last edited by royUK; 02-15-2009 at 03:56 AM.
To Leith,
Thank you. However, I saved a copy of your code and typed some test data in cell A1 in Sheet1 and I am still getting the same error message. When you made your changes, to see you new toggle buttons with the caption from the data you entered in cell A1??
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.
Last edited by Sahak; 02-16-2009 at 06:46 PM.
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")
And the second part of the code I usedPlease 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.Please Login or Register to view this content.
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.
Last edited by siamadu; 02-16-2009 at 09:20 PM.
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.
I removed the following code from the workbook as it is not needed.Please Login or Register to view this content.
Please Login or Register to view this content.
Thank you so much!! It works perfectly. And thank you for taking out the time to revise my actual code.
You guys are a real blessing!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks