+ Reply to Thread
Results 1 to 4 of 4

Passing values to a second user form.

Hybrid View

  1. #1
    Registered User
    Join Date
    08-27-2014
    Location
    Albany, NY
    MS-Off Ver
    Office 2010
    Posts
    91

    Angry Passing values to a second user form.

    I can't even comprehend how to attack this so I wanted to put it out there and see if something sparks.

    I have a user form that dynamically pulls row values based on a users comboBox selection. and I have set a section for user notes based on the dynamic row value and places it in column L. Of course it works awesome and the variables all sync well...however my boss now wants the notes to pop up on a second user form when the user click "Update" (ok no problem, 2nd user form piece of cake), but the row is dynamically named in userform one, how can I pass that to the note's text box in Userform two and keep the variables true...am I a block head?

    Userform 1
    Option Explicit
        'Declare module level variables
        Dim bNormalProcess As Boolean
        Dim bcol2Valid As Boolean
        Dim temp As String
    
    Private Sub UserForm_Initialize()
       'Initialize variables
        Me.updateButton.Enabled = False
        bcol2Valid = False
        bNormalProcess = True
        Dim rngColumn1 As Range
        Dim ws As Worksheet
       
        ' Populate combo box list that will expand as the range expands.
         Set ws = Worksheets("Master_list")
         For Each rngColumn1 In ws.Range("_Col1")
        
         Me.cboCol1.AddItem rngColumn1.Value
        
         Next rngColumn1
    lbl_exit:
        Exit Sub
    End Sub
    Private Sub cboCol1_Change()
    
        Dim ws As Worksheet
        Set ws = Worksheets("Master_list")
            
            
          
        ' set the text box values with corresponding places on the spreadsheet based on combo box.
        With Me
            .TextBox1.Value = ws.Cells(.cboCol1.ListIndex + 5, 2)
            .TextBox2.Value = ws.Cells(.cboCol1.ListIndex + 5, 3)
            .TextBox3.Value = ws.Cells(.cboCol1.ListIndex + 5, 4)
            .TextBox4.Value = ws.Cells(.cboCol1.ListIndex + 5, 5)
            .TextBox5.Value = ws.Cells(.cboCol1.ListIndex + 5, 6)
            .TextBox6.Value = ws.Cells(.cboCol1.ListIndex + 5, 7)
            .TextBox7.Value = ws.Cells(.cboCol1.ListIndex + 5, 8)
            .TextBox8.Value = ws.Cells(.cboCol1.ListIndex + 5, 9)
            .TextBox9.Value = ws.Cells(.cboCol1.ListIndex + 5, 10)
            
            'Store the address values for each control in the .Tag attribute
            '(False, False) stores the address as 'A1' instead of '$A$1'
            .cboCol1.Tag = ws.Cells(.cboCol1.ListIndex + 5, 1).Address(False, False)
            .TextBox1.Tag = ws.Cells(.cboCol1.ListIndex + 5, 2).Address(False, False)
            .TextBox2.Tag = ws.Cells(.cboCol1.ListIndex + 5, 3).Address(False, False)
            .TextBox3.Tag = ws.Cells(.cboCol1.ListIndex + 5, 4).Address(False, False)
            .TextBox4.Tag = ws.Cells(.cboCol1.ListIndex + 5, 5).Address(False, False)
            .TextBox5.Tag = ws.Cells(.cboCol1.ListIndex + 5, 6).Address(False, False)
            .TextBox6.Tag = ws.Cells(.cboCol1.ListIndex + 5, 7).Address(False, False)
            .TextBox7.Tag = ws.Cells(.cboCol1.ListIndex + 5, 8).Address(False, False)
            .TextBox8.Tag = ws.Cells(.cboCol1.ListIndex + 5, 9).Address(False, False)
            .TextBox9.Tag = ws.Cells(.cboCol1.ListIndex + 5, 10).Address(False, False)
             temp = CStr(.cboCol1.ListIndex + 5)
             TextBox1.SetFocus
             
        End With
    End Sub
    Private Sub cancelButton_Click()
        Unload UserForm1
        MsgBox "No Changed Made", vbCritical
    End Sub
    
     Private Sub updateButton_Click()
        Call PutUserForm1DataBackInWorksheet
        Unload UserForm1
        'UserForm2.Show
        'Sheets("Master_list").Activate
    End Sub
    
    Sub PutUserForm1DataBackInWorksheet()
    
      Dim ws As Worksheet
      Set ws = Worksheets("Master_list")
        
      Dim sAddress As String
      Dim sValue As String
      
      'Get the Worksheet Cell Address
      'Get the value from the UserForm
      'Put the value in the Worksheet
      
      'ComboBox1
      sAddress = UserForm1.cboCol1.Tag
      sValue = Trim(UserForm1.cboCol1.Text)
      ws.Range(sAddress).Value = sValue
      
     'TextBox1
      sAddress = UserForm1.TextBox1.Tag
      sValue = Trim(UserForm1.TextBox1.Text)
      ws.Range(sAddress).Value = sValue
      
      'TextBox2
      sAddress = UserForm1.TextBox2.Tag
      sValue = Trim(UserForm1.TextBox2.Text)
      ws.Range(sAddress).Value = sValue
      
      'TextBox3
      sAddress = UserForm1.TextBox3.Tag
      sValue = Trim(UserForm1.TextBox3.Text)
      ws.Range(sAddress).Value = sValue
      
      'TextBox4
      sAddress = UserForm1.TextBox4.Tag
      sValue = Trim(UserForm1.TextBox4.Text)
      ws.Range(sAddress).Value = sValue
      
      'TextBox5
      sAddress = UserForm1.TextBox5.Tag
      sValue = Trim(UserForm1.TextBox5.Text)
      ws.Range(sAddress).Value = sValue
      
      'TextBox6
      sAddress = UserForm1.TextBox6.Tag
      sValue = Trim(UserForm1.TextBox6.Text)
      ws.Range(sAddress).Value = sValue
      
      'TextBox6
      sAddress = UserForm1.TextBox7.Tag
      sValue = Trim(UserForm1.TextBox7.Text)
      ws.Range(sAddress).Value = sValue
      
      'TextBox7
      sAddress = UserForm1.TextBox8.Tag
      sValue = Trim(UserForm1.TextBox8.Text)
      ws.Range(sAddress).Value = sValue
       
      
      'TextBox8
      sAddress = UserForm1.TextBox9.Tag
      sValue = Trim(UserForm1.TextBox9.Text)
      ws.Range(sAddress).Value = sValue
      
      
      'Get dynamic row and target it at Column K to datestamp it
      sAddress = "k" + temp
      sValue = DateTime.Now
      ws.Range(sAddress).Value = sValue
      
      'Get dyanic Row and target it at Column L to allow for user notes
      sAddress = "l" + temp
      sValue = Trim(UserForm1.TextBox10.Text)
      ws.Range(sAddress).Value = sValue
      
      Set ws = Nothing
    
    End Sub
    Private Sub textBox1_Enter()
        Call textBox1_Change
    lbl_exit:
        Exit Sub
    End Sub
    Private Sub textBox1_Change()
        'In all cases Assume invalid on change
        bcol2Valid = False
        With Me
            'display instructional label text.
            .instructionsLabel.Caption = "Change your 6 digit Historical Provider Number.  Place Zeroes to the left of the number to make it six digits."
            .instructionsLabel.ForeColor = vbBlue
            .instructionsLabel.Font.Bold = True
            'Clear Alert text
            .alertsLabel.Caption = " "
            .alertsLabel.BackColor = vbButtonFace
            'Does this change validate this field?
            If Len(.TextBox1) = 6 Then
                bcol2Valid = True
                .instructionsLabel.Caption = "Historical Provider Number, which should be a 6 digit number."
                .instructionsLabel.ForeColor = vbBlue
                .instructionsLabel.Font.Bold = True
            End If
        End With
        'Does this change validate or invalidate this form?
        ValidateForm
    lbl_exit:
        Exit Sub
    End Sub
    Private Sub textBox1_Exit(ByVal cancel As MSForms.ReturnBoolean)
        cancel = True
        'keep focus on the invalid field
        If Not bcol2Valid = True Then
            cancel = True
            'Changes the alert box to show what error may be and highlight the background and text
            Me.alertsLabel.Caption = "The Historical Provider Number Provided is incorrect" & vbCr + vbCr & "Please reenter the 6 digit number."
            Me.alertsLabel.ForeColor = vbRed
            Me.alertsLabel.BackColor = vbYellow
            Me.alertsLabel.Font.Bold = True
        End If
        'Checks to make sure the box was not left balnk
        If TextBox1.Value = "" Then
            MsgBox "This entry cannot be left blank."
        End If
    lbl_exit:
        Exit Sub
    End Sub
    Sub ValidateForm()
        Dim bValid As Boolean
            'true when all variable multiples are true
            bValid = bcol2Valid
        If bValid Then
            Me.updateButton.Enabled = True
        Else
            Me.updateButton.Enabled = False
        End If
    lbl_exit:
        Exit Sub
    End Sub
    Master List v2.63.xls

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Passing values to a second user form.

    You will need to publicly declare the variables and then in the UserFrom2_Initialize populate your textbox using said variable.

    Example:
    Option Explicit
        'Declare module level variables
        Dim bNormalProcess As Boolean
        Dim bcol2Valid As Boolean
        Dim temp As String
        Public dynamicrowname as String 'this is the variable that you referred to in your post
    
    Private Sub UserForm_Initialize()

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,330

    Re: Passing values to a second user form.

    Hi Coleman,

    I'm pretty lazy and always try to use a non vba solution first. Have you ever heard of or seen the Excel (built in) Form tool? You can add the icon for it on the Quick Access Toolbar and use it instead of your form. See:
    http://office.microsoft.com/en-us/ex...010236698.aspx

    Now - if you really need the dropdown to select a certain record, I'd grab the Enhanced Forms Tool off the site:
    http://spreadsheetpage.com/index.php/dataform/home - pay to see the code and see if you can modify it for your non-commercial use.

    Then I'd have a small talk with your boss on how to use standard tools instead of having you create unique ones.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    08-27-2014
    Location
    Albany, NY
    MS-Off Ver
    Office 2010
    Posts
    91

    Re: Passing values to a second user form.

    Simplicity in work is always overlooked. I will see what I can talk my boss in to/out of.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] User Form to execute search and return all values to the user form for editing
    By allwrighty in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-11-2013, 10:40 PM
  2. Passing Values from User Form
    By srinivassurapareddi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-28-2012, 01:49 PM
  3. Passing inputs from one user form to another
    By daymaker in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-18-2011, 11:02 AM
  4. Passing variables from sub to user form
    By boc_est1986 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-18-2009, 09:09 AM
  5. User Form value passing problem
    By brusk2@uiuc.edu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-09-2006, 12:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1