Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.
Hi all, please see this major site announcement: https://www.boards.ie/discussion/2058427594/boards-ie-2026

VB Form Texbox Processing

  • 26-10-2009 10: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

  • #2
    Ginger
    Registered Users, Registered Users 2 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


  • #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.


  • #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!


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


    got it:

    If TypeOf ctl Is MSforms.TextBox then

    Thanks for the help!


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


    Ah right the other one was straight VB rather than VBA


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


    Also can you marked this as solved please


  • #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