neede help for creating commandbutton during runtime error

**aaronkoh** · 01-01-2013, 04:40 AM

0down votefavorite

i have a module that creates command buttons suring runtime. it will create the commandbuttons in a specificed userform

program works fine, when i execute the module.

However, when i use the userform to call the module instead, i have a error stating

Run-time error '91': Object variable or With block variable not set

Please Login or Register  to view this content.

End Sub
Kindly advise.

**patel45** · 01-01-2013, 04:57 AM

attach file, I can not test without

**aaronkoh** · 01-01-2013, 05:42 AM

Hi, have attached the codes.

module1 is where i run my code. when i run it in module1, it creates the commandbutton in form1.

when i run form1, i can click on the submit buttons.

but when i try to call the module from form1 instead, i get the error.

**Norie** · 01-01-2013, 06:42 AM

Why are you creating controls at runtime?

**patel45** · 01-01-2013, 06:46 AM

your code works, but see http://www.eng-tips.com/faqs.cfm?fid=110
you have to activate library and set workbook macro protection to developers setting

**aaronkoh** · 01-01-2013, 10:37 AM

Hi,tried adding the reference, and ensuring that all macros are allowed.

However, i still recieve the same error.

**patel45** · 01-01-2013, 11:16 AM

it is not sufficient, Excel Options. In the Trust Center category, click Trust Center Settings, and then click the Macro Settings category, developers setting

**aaronkoh** · 01-01-2013, 12:12 PM

Hi patel.

i tried clicking the developer's macro settings checkbox, and re run the code. however, i still face the same problem if i call the module from the userform.

if i run it straight from the module, all is fine.

Thank you

**aaronkoh** · 01-01-2013, 12:16 PM

Hi Norie.

i was thinking of creating a page, that allows user to specify the number of fields he wants to key in.
hence i would like to create dynamic textboxes, as well as commandbuttons to read inputs.

i have managed to create, and attach event handlers to the command buttons, however, i can only run it successfully from a module.

if i use the userform to call that module, then the error occurs.

Thank you

**Norie** · 01-01-2013, 01:04 PM

Why not use a listbox, textbox(es) and command button?

The user enters data in the textbox(es), presses the command button to transfer the data to the listbox and clear the textbox(es) ready for the next input.

**patel45** · 01-01-2013, 02:27 PM

Originally Posted by aaronkoh

i still face the same problem if i call the module from the userform.

I don't understand, how you call module from the userform ? did you attach the correct file ?

**Norie** · 01-01-2013, 02:38 PM

If you want to add command buttons, or any other control, at runtime you can use something like this.

Please Login or Register  to view this content.

Controls added in this way won''t persist, but it doesn't sound like you need them to.

As for code for these controls, you can use a class module for that.

By the way, have you considered that not all people might be able to run code like you posted due to security settings.

**aaronkoh** · 01-03-2013, 08:44 AM

Hi Norie.

I didnt set the textboxes and command buttons before hand, as i want it to be dynamic.

e.g. if i hardcode 5 textboxes, and in the event that i instead have 10 strings to show, then i will have to re-write the codes. "inclusion of naother 5 textboxes".

Hi patel, in the UserForm page, i added "AddButtonAndShow2()".

the module "AddButtonAndShow2()" works fine on its own when run. but when i called it from UserForm1, the error appears.

**aaronkoh** · 01-03-2013, 09:23 AM

Hi Norie.

I didnt set the textboxes and command buttons before hand, as i want it to be dynamic.

e.g. if i hardcode 5 textboxes, and in the event that i instead have 10 strings to show, then i will have to re-write the codes. "inclusion of naother 5 textboxes".

Hi patel, in the UserForm page, i added "AddButtonAndShow2()".

the module "AddButtonAndShow2()" works fine on its own when run. but when i called it from UserForm1, the error appears.

**OllieB** · 01-03-2013, 09:36 AM

Please have a look at the link enclosed

http://stackoverflow.com/questions/1...-to-a-userform

**OllieB** · 01-03-2013, 09:40 AM

Or create a userform from scratch using VBA

http://j-walk.com/ss/excel/tips/modGetOption.bas

**Norie** · 01-03-2013, 09:41 AM

I'm not suggesting setting anything beforehand, if you use code like I posted you will create buttons at run-time.

What you are currently doing isn't creating buttons at run-time, the buttons you create will still be on the form after it's closed.

Also, every time you run the code you'll be adding new buttons to the already existing buttons.

For example, if the first time you run it 10 buttons are added and the next time 15 buttons are added you'll have a total of 25 buttons on the form.

Have a look at the attached workbook which has a userform with controls created at runtime.