VB Form Texbox Processing

cunnins4

Hi folks,

I've a bit of experience with VB using forms in Excel, and I'm trying to create multiple forms and process user input. I'm trying to capture a user's input in a textbox to a spreadsheet and I'm trying to use the For Each command and I've got this:

Dim Count As Integer
Count = 1

For Each Textbox In Form.Controls

ActiveSheet.Range("A" & Count).Value = Textbox

Count = Count + 1

Next Textbox

Count = 1

the problem is it captures ALL of the info on the form including labels and command buttons. I just want what the user has entered in the textboxes.

I'm afraid my knowledge and understanding is limited, so if anyone could lend a hand I'd be most appreciative.

Ginger

Right in that statement what you are doing is assigning the name TextBox to every control and using that

So what you need

Dim Count As Integer
Count = 1

Dim control As Control

For Each control In Form.Controls

If TypeOf control Is TextBox Then
        ActiveSheet.Range("A" & Count).Value = control.Text
End If

Count = Count + 1

Next control

Count = 1 

that should do it

cunnins4

ah I see-that makes sense!

thanks a mil-I'll try this when I get back to it later today.

cunnins4

Hmmm...it won't work for labels or textboxes but works for commandbuttons!

this is the exact code I have for testing it-I'm just using a msgbox as an easy test of the if statement, i'll worry about the text later. For now I just want to differentiate between different controls:

Dim ctl As Control


For Each ctl In Form.Controls

If TypeOf ctl Is TextBox Then

MsgBox ("hello")

End If

Next ctl

works perfect if I swap "commandbutton" for "textbox"!!!Strange!

cunnins4

got it:

If TypeOf ctl Is MSforms.TextBox then

Thanks for the help!

Ginger

Ah right the other one was straight VB rather than VBA

Ginger

Also can you marked this as solved please

cunnins4

Ginger wrote: »

Ah right the other one was straight VB rather than VBA

Sorry I didn't realise there was a difference-your comment made me rethink how I was going about this and I took a look at visual studio instead of the visual basic editor built into excel and i've decided to write it in VB instead of VBA and make a small application out of it-that way it should be able to run on all the computers I need it to, output the results to a text file and I should be brand new!

Thanks for the help!