Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

VB Form Texbox Processing

Options
  • 26-10-2009 11:35pm
    #1
    cunnins4
    Closed Accounts Posts: 882 ✭✭✭


    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.


Comments

  • Options
    #2
    Ginger
    Registered Users Posts: 2,931 ✭✭✭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


  • Options
    #3
    cunnins4
    Closed Accounts Posts: 882 ✭✭✭cunnins4


    ah I see-that makes sense!

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


  • Options
    #4
    cunnins4
    Closed Accounts Posts: 882 ✭✭✭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!


  • Options
    #5
    cunnins4
    Closed Accounts Posts: 882 ✭✭✭cunnins4


    got it:

    If TypeOf ctl Is MSforms.TextBox then

    Thanks for the help!


  • Options
    #6
    Ginger
    Registered Users Posts: 2,931 ✭✭✭Ginger


    Ah right the other one was straight VB rather than VBA


  • Advertisement
  • Options
    #7
    Ginger
    Registered Users Posts: 2,931 ✭✭✭Ginger


    Also can you marked this as solved please


  • Options
    #8
    cunnins4
    Closed Accounts Posts: 882 ✭✭✭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!


Advertisement