generate textbox on button click

[lyntan]

hello! please help!
how to generate textbox on button click?
i got this code :
ActiveSheet.Shapes.AddLabel(msoTextOrientationHorizontal, 2800, 900, 100, 30).Select

it works but its a plain box without background colour or border.
i need a coloured textbox on button click in excel.
please help ;(

 

[My Aswer Is This]

I suggest you Run the Macro Recorder and make the Text Box exactly the way you want. Move the text box and next time you want another text box just run the script.

 

[lyntan]

I suggest you Run the Macro Recorder and make the Text Box exactly the way you want. Move the text box and next time you want another text box just run the script.

sorry what do you mean by macro recorder? im new to excel.

 

[My Aswer Is This]

On your ribbon click on view. Look on right and you should see Macros. Choose Record Macro. Now there is a recorder that will record every thing you do. When done click macros and choose Stop. Then you can assign this macro to a button to run it. I'm surprised you know how to write install and run scripts but are not familiar with the recorder. Check back if you do not understand me.

 

[lyntan]

On your ribbon click on view. Look on right and you should see Macros. Choose Record Macro. Now there is a recorder that will record every thing you do. When done click macros and choose Stop. Then you can assign this macro to a button to run it. I'm surprised you know how to write install and run scripts but are not familiar with the recorder. Check back if you do not understand me.

oh yes! it works! when i click the button the coloured text box appeared ! thank you!
but it only works once.
when i click the button again it shows error.

 

[My Aswer Is This]

You ran the recorder and made you box. You stopped the recorder. And then assigned the script to a button and it ran once but the second time it errored out is that what your saying. Try running the script from the view Macro run Macro on the ribbon and see if it works OK

 

[lyntan]

You ran the recorder and made you box. You stopped the recorder. And then assigned the script to a button and it ran once but the second time it errored out is that what your saying. Try running the script from the view Macro run Macro on the ribbon and see if it works OK

'object does not support this property or method'

 

[My Aswer Is This]

I would suggest running the recorder again. Make a new box and try it again. Try running the script a few times from the macro menu on the ribbon if it works ok try assigning it to a button.

 

[lyntan]

I would suggest running the recorder again. Make a new box and try it again. Try running the script a few times from the macro menu on the ribbon if it works ok try assigning it to a button.

erm to confirm with you, when you said 'make a new box' you meant starting from button click and it generate a textbox to design the text box? and then stop the recording?
or i make a textbox beforehand and start recording from designing the box and stop after designing?

 

[lyntan]

I would suggest running the recorder again. Make a new box and try it again. Try running the script a few times from the macro menu on the ribbon if it works ok try assigning it to a button.

oh i realised if i assign the macro to my button . it only works if i have an existing textbox. then when i click the button it changes the colour of the textbox.
but what i want is when i click a button, it generates a textbox with colour.

i tried to start recording from the button click but strangely my button click doesnt works when i recording a macro.