Convert a string to a TextBox name that VBA recognises

[Ian Fraser]

' I want to loop through TextBoxes and get their values
' In this simple test case, to insert them in spreadsheet cells A1 and A2

' VBA does nor recognize a string as a Textbox name and I have not been
' able to find a way to make this work.

' Any help would be very much appreciated!

Please Login or Register  to view this content.

[Norie]

Where are the textboxes located?

PS Can you add code tags when posting code?

[Ian Fraser]

In Sheet1 right now, later in a UserForm

Please Login or Register  to view this content.

[Norie]

You can use a string to refer to a control on a sheet or userform but the syntax isn't the same for both.

Furthermore, if they are on a sheet the syntax depends on which type of control they are, Forms or ActiveX.

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

PS The colours are because you've used the tags for PHP code, not exactly quite sure what each colour represents.

If you use 'standard' code tags you won't get the colours.

[Ian Fraser]

Norie,

the spreadsheet is attached to the 10:55am post above.

I'm using ActiveX controls. Got rid of the colored code

Thanks for your help!

[Marc L]

Just read this thread …

[Ian Fraser]

Marc,

when I add "Controls" to the line Range(strRange).Value = Controls("TextBox" & CStr(iCount)).Value

I get an error message: "Compile Error Sub or Function not defined" and the word "Controls" is highlighted.

[LJMetzger]

Hi Ian,

The following routines will display output in the Immediate Window (CTRL G) in the debugger.

Try this code for Active X controls:

Please Login or Register  to view this content.

Use the following for a UserForm:

Please Login or Register  to view this content.

Lewis

[Leith Ross]

Hello Ian Fraser,

Here is the corrected and working macro code.

Please Login or Register  to view this content.