Can a UserForm retain info?

leopardhawk

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
Office Version
  1. 2016
Platform
  1. Windows
Hello forum friends, I have a fairly complex workbook that has two UserForms activated by Command Buttons where users enter Personal and Financial info respectively. I will use the Personal Info UserForm as an example:

The user clicks the button and the UserForm opens, and at a minimum they enter their first and last name, date of birth and gender in separate fields. When they click the OK button in the form, the data they entered is transferred to the appropriate worksheet(s) and the UserForm is cleared. I am wondering if there is a way to code it so that the UserForm will 'retain' the data that was entered the first time so that if the user launches it again, it is still visible to them.

Of course the same applies to the Financial Info UserForm.

Appreciate any help!
 
Sort by date Sort by votes
@JEC - yes, the data is definitely stored within the workbook. If this is possible, would also like to know how to do it.
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
If no one else jumps in first, maybe tomorrow I can give an example Userform.
 
Upvote 0
@wsnyder thanks Winston for the link but I still don't have the skills to integrate your code within mine so that it would work error free. I'm assuming that I would have to make quite a few changes and wouldn't even know where to begin... ?

I do appreciate you reaching out though!

Best!
 
Upvote 0
Still looking for some help if anyone knows how I might accomplish this. I am attaching the VBA code and an image of the UserForm if that will help. To recap, the UserForm is designed so that the user can enter data into specific sheets and cells throughout the workbook. When they click to OK button on this UserForm, it sends the data to different cells on Sheet11 of the workbook. Users may want to do this over long periods of time so there will be instances where the workbook is saved and closed.

What I would like to have happen is that when they reopen the workbook and launch the UserForm, any previously entered data will be displayed in the textboxes and or comboboxes that they had already filled in. All this of course IF it is possible.

Appreciate any help!


Capture.JPG


VBA Code: 
Private Sub CloseCommandButton_Click()

Unload Me

End Sub

Private Sub ComboBox4_Change()

End Sub

Private Sub GenderComboBox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   With Me.GenderComboBox
      If .Text = "M" Or .Text = "F" Or .Text = "" Then
         .BackColor = rgbWhite
         Label4.Caption = "Gender"
         Label4.ForeColor = rgbBlack
      Else
         Label4.Caption = "Please select M or F"
         Label4.ForeColor = RGB(255, 55, 55)
         .BackColor = RGB(255, 55, 55)
         GenderComboBox.Value = ""
      End If
   End With
End Sub

Private Sub SGenderComboBox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   With Me.SGenderComboBox
      If .Text = "M" Or .Text = "F" Or .Text = "" Then
         .BackColor = rgbWhite
         Label24.Caption = "Gender"
         Label24.ForeColor = rgbBlack
      Else
         Label24.Caption = "Please select M or F"
         Label24.ForeColor = RGB(255, 55, 55)
         .BackColor = RGB(255, 55, 55)
         SGenderComboBox.Value = ""
      End If
   End With
End Sub

Private Sub UserForm_Initialize()
Dim objControl As MSForms.Control

    For Each objControl In Me.Controls
        If TypeName(objControl) = "TextBox" And objControl.Tag <> "" Then
           Me.setupPlaceholder objControl.Name, False
        End If
    Next objControl

    MultiPage1.Value = 0
    Me.FirstNameTextBox.SetFocus
    Me.FirstNameTextBox.Value = Sheet11.Range("C9").Value
    
        
'Fill all 'Month' combo boxes with valid months
Dim MonthAry(1 To 12) As Variant, ComboAry As Variant
    Dim i As Long
   
        ComboAry = Array("B", "R", "C", "O", "SB", "SR", "SC", "SO")
          For i = 1 To 12
        MonthAry(i) = MonthName(i)
    Next i
        For i = 0 To UBound(ComboAry)
        Me.Controls(ComboAry(i) & "Month").List = MonthAry
    Next i
    
'Fill all 'Day' combo boxes with valid days
Dim Arry As Variant
    
    Arry = Evaluate("if({1},row(1:31))")
        Me.BDay.List = Arry
        Me.RDay.List = Arry
        Me.CDay.List = Arry
        Me.ODay.List = Arry
        Me.SBDay.List = Arry
        Me.SRDay.List = Arry
        Me.SCDay.List = Arry
        Me.SODay.List = Arry
    
'Fill all 'Year' combo boxes with valid years
Dim Ary As Variant
    
    Ary = Evaluate("if({1},row(1930:2130))")
        Me.BYear.List = Ary
        Me.RYear.List = Ary
        Me.CYear.List = Ary
        Me.OYear.List = Ary
        Me.SBYear.List = Ary
        Me.SRYear.List = Ary
        Me.SCYear.List = Ary
        Me.SOYear.List = Ary
    
'Fill 'Gender' combo box with valid genders
    Me.GenderComboBox.List = Array("M", "F")
    Me.GenderComboBox.Style = fmStyleDropDownCombo
    
'Force users to choose from combo boxes
        Me.BMonth.Style = 2
        Me.BDay.Style = 2
        Me.BYear.Style = 2
        
        Me.SBMonth.Style = 2
        Me.SBDay.Style = 2
        Me.SBYear.Style = 2
        
        Me.RMonth.Style = 2
        Me.RDay.Style = 2
        Me.RYear.Style = 2
        
        Me.SRMonth.Style = 2
        Me.SRDay.Style = 2
        Me.SRYear.Style = 2
        
        Me.CMonth.Style = 2
        Me.CDay.Style = 2
        Me.CYear.Style = 2
        
        Me.SCMonth.Style = 2
        Me.SCDay.Style = 2
        Me.SCYear.Style = 2
        
        Me.OMonth.Style = 2
        Me.ODay.Style = 2
        Me.OYear.Style = 2
        
        Me.SOMonth.Style = 2
        Me.SODay.Style = 2
        Me.SOYear.Style = 2
    
'Fill 'Pension Provider' combo boxes with list of Canadian pension providers
    Dim LastRow As Long
    Dim SheetName As String
    SheetName = "Sheet20"
    LastRow = Sheets(SheetName).Cells(Rows.Count, "A").End(xlUp).Row
    Me.ProviderComboBox.List = Sheets("Sheet20").Range("A2:A" & LastRow).Value
    Me.SProviderComboBox.List = Sheets("Sheet20").Range("A2:A" & LastRow).Value
    
'Fill 'Pension Option' combo boxes with list of popular pension options
    Me.OptionComboBox.List = Array("100% Joint Life", _
        "100% Joint Life 5-year guarantee", "100% Joint Life 10-year guarantee", "100% Joint Life 15-year guarantee", _
        "75% Joint Life 5-year guarantee", "75% Joint Life 10-year guarantee", "75% Joint Life 15-year guarantee", _
        "60% Joint Life 5-year guarantee", "60% Joint Life 10-year guarantee", "60% Joint Life 15-year guarantee", _
        "Single Life", _
        "Single Life 5-year guarantee", "Single Life 10-year guarantee", "Single Life 15-year guarantee", _
        "Other")

'Fill 'Spouse/Partner Gender' combo box with valid genders
    Me.SGenderComboBox.List = Array("M", "F")
    Me.SGenderComboBox.Style = fmStyleDropDownCombo
    
'Fill 'Spouse/Partner Pension Option' combo boxes with list of popular pension options
    Me.SOptionComboBox.List = Array("100% Joint Life", _
        "100% Joint Life 5-year guarantee", "100% Joint Life 10-year guarantee", "100% Joint Life 15-year guarantee", _
        "75% Joint Life 5-year guarantee", "75% Joint Life 10-year guarantee", "75% Joint Life 15-year guarantee", _
        "60% Joint Life 5-year guarantee", "60% Joint Life 10-year guarantee", "60% Joint Life 15-year guarantee", _
        "Single Life", _
        "Single Life 5-year guarantee", "Single Life 10-year guarantee", "Single Life 15-year guarantee", _
        "Other")

End Sub

'Set focus to first available box on each tab of the multi-page userForm
Private Sub MultiPage1_Change()

    GetTextBoxByTabIndex(Me.MultiPage1.SelectedItem, 0).SetFocus

End Sub

Private Function GetTextBoxByTabIndex(ByVal ControlParent As Object, ByVal TabIndex As Long) As Control

    Dim oCtrl As Control
    
    For Each oCtrl In ControlParent.Controls
        If TypeOf oCtrl Is MSForms.TextBox Then
            If oCtrl.TabIndex = TabIndex Then
                Set GetTextBoxByTabIndex = oCtrl
                Exit For
            End If
        End If
    Next oCtrl

End Function

Private Sub OKCommandButton_Click()

'Combine month, day, year into valid dates
    Dim DOB As Variant
        On Error Resume Next
        DOB = DateSerial(Me.BYear, Month(DateValue(Me.BMonth & "/01/2020")), Me.BDay)
        On Error GoTo 0
    
    Dim RD As Variant
        On Error Resume Next
        RD = DateSerial(Me.RYear, Month(DateValue(Me.RMonth & "/01/2020")), Me.RDay)
        On Error GoTo 0
                   
    Dim CSD As Variant
        On Error Resume Next
        CSD = DateSerial(Me.CYear, Month(DateValue(Me.CMonth & "/01/2020")), Me.CDay)
        On Error GoTo 0
                   
    Dim OSD As Variant
        On Error Resume Next
        OSD = DateSerial(Me.OYear, Month(DateValue(Me.OMonth & "/01/2020")), Me.ODay)
        On Error GoTo 0
        
    Dim SDOB As Variant
        On Error Resume Next
        SDOB = DateSerial(Me.SBYear, Month(DateValue(Me.SBMonth & "/01/2020")), Me.SBDay)
        On Error GoTo 0
        
    Dim SRD As Variant
        On Error Resume Next
        SRD = DateSerial(Me.SRYear, Month(DateValue(Me.SRMonth & "/01/2020")), Me.SRDay)
        On Error GoTo 0
                   
    Dim SCSD As Variant
        On Error Resume Next
        SCSD = DateSerial(Me.SCYear, Month(DateValue(Me.SCMonth & "/01/2020")), Me.SCDay)
        On Error GoTo 0
                   
    Dim SOSD As Variant
        On Error Resume Next
        SOSD = DateSerial(Me.SOYear, Month(DateValue(Me.SOMonth & "/01/2020")), Me.SODay)
        On Error GoTo 0
                   
    With Sheets(11)

'Send data to sheets
                
        If Not AllmostEmpty(FirstNameTextBox) Then .Range("C9").Value = FirstNameTextBox.Value
              
        If LastNameTextBox <> "Optional" Then
            If Not AllmostEmpty(LastNameTextBox) Then .Range("D9").Value = LastNameTextBox.Value
        End If
        
        If IsDate(DOB) Then .Range("E9").Value = DOB
        If Not AllmostEmpty(GenderComboBox) Then .Range("F9").Value = GenderComboBox.Value
        
        If CompanyTextBox <> "Optional" Then
            If Not AllmostEmpty(CompanyTextBox) Then .Range("G9").Value = CompanyTextBox.Value
        End If
        
        If IsDate(RD) Then .Range("C15").Value = RD
        If Not AllmostEmpty(OptionComboBox) Then .Range("D15").Value = OptionComboBox.Value
        If Not AllmostEmpty(ProviderComboBox) Then .Range("E15").Value = ProviderComboBox.Value
        If IsDate(CSD) Then .Range("F15").Value = CSD
        If IsDate(OSD) Then .Range("G15").Value = OSD
        
        
    End With
        
    With Sheets(11)

               
        If Not AllmostEmpty(SFirstNameTextBox) Then .Range("C11").Value = SFirstNameTextBox.Value
        
        If SLastNameTextBox <> "Optional" Then
            If Not AllmostEmpty(SLastNameTextBox) Then .Range("D11").Value = SLastNameTextBox.Value
        End If
        
        If IsDate(SDOB) Then .Range("E11").Value = SDOB
        If Not AllmostEmpty(SGenderComboBox) Then .Range("F11").Value = SGenderComboBox.Value
        
        If SCompanyTextBox <> "Optional" Then
            If Not AllmostEmpty(SCompanyTextBox) Then .Range("G11").Value = SCompanyTextBox.Value
        End If
        
        If IsDate(SRD) Then .Range("C17").Value = SRD
        If Not AllmostEmpty(SOptionComboBox) Then .Range("D17").Value = SOptionComboBox.Value
        If Not AllmostEmpty(SProviderComboBox) Then .Range("E17").Value = SProviderComboBox.Value
        If IsDate(SCSD) Then .Range("F17").Value = SCSD
        If IsDate(SOSD) Then .Range("G17").Value = SOSD
        
            
    End With
    
Unload Me
    
End Sub

'Set textbox tags to gray
Sub setupPlaceholder(txtBox As String, focus As Boolean)
    With Me.Controls(txtBox)
        If Len(.Text) = 0 And Not focus Then
            .Text = .Tag
            .ForeColor = vbGrayText
        ElseIf .Text = .Tag Then
            .Text = ""
            .ForeColor = vbWindowText
        End If
    End With
End Sub

Private Sub LastNameTextBox_Enter()
    setupPlaceholder LastNameTextBox.Name, True
End Sub

Private Sub LastNameTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder LastNameTextBox.Name, False
End Sub

Private Sub CompanyTextBox_Enter()
    setupPlaceholder CompanyTextBox.Name, True
End Sub

Private Sub CompanyTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder CompanyTextBox.Name, False
End Sub

Private Sub SLastNameTextBox_Enter()
    setupPlaceholder SLastNameTextBox.Name, True
End Sub

Private Sub SLastNameTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder SLastNameTextBox.Name, False
End Sub

Private Sub SCompanyTextBox_Enter()
    setupPlaceholder SCompanyTextBox.Name, True
End Sub

Private Sub SCompanyTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder SCompanyTextBox.Name, False
End Sub

'Limit number of characters in each textbox or combobox
Private Sub ProviderComboBox_Change()
    If ProviderComboBox.TextLength > 42 Then
    MsgBox "Please limit your input to 42 characters."
    ProviderComboBox.Text = Left(ProviderComboBox, ProviderComboBox.TextLength - 1)
    End If
End Sub

Private Sub SProviderComboBox_Change()
    If SProviderComboBox.TextLength > 42 Then
    MsgBox "Please limit your input to 42 characters."
    SProviderComboBox.Text = Left(SProviderComboBox, SProviderComboBox.TextLength - 1)
    End If
End Sub

Private Sub OptionComboBox_Change()
    If OptionComboBox.TextLength > 34 Then
    MsgBox "Please limit your input to 34 characters."
    OptionComboBox.Text = Left(OptionComboBox, OptionComboBox.TextLength - 1)
    End If
End Sub

Private Sub SOptionComboBox_Change()
    If SOptionComboBox.TextLength > 34 Then
    MsgBox "Please limit your input to 34 characters."
    SOptionComboBox.Text = Left(SOptionComboBox, SOptionComboBox.TextLength - 1)
    End If
End Sub

Private Sub FirstNameTextBox_Change()
    If FirstNameTextBox.TextLength > 13 Then
    MsgBox "Please limit your input to 13 characters."
    FirstNameTextBox.Text = Left(FirstNameTextBox, FirstNameTextBox.TextLength - 1)
    End If
End Sub

Private Sub SFirstNameTextBox_Change()
    If SFirstNameTextBox.TextLength > 13 Then
    MsgBox "Please limit your input to 13 characters."
    SFirstNameTextBox.Text = Left(SFirstNameTextBox, SFirstNameTextBox.TextLength - 1)
    End If
End Sub

Private Sub LastNameTextBox_Change()
    If LastNameTextBox.TextLength > 28 Then
    MsgBox "Please limit your input to 28 characters."
    LastNameTextBox.Text = Left(LastNameTextBox, LastNameTextBox.TextLength - 1)
    End If
End Sub

Private Sub SLastNameTextBox_Change()
    If SLastNameTextBox.TextLength > 28 Then
    MsgBox "Please limit your input to 28 characters."
    SLastNameTextBox.Text = Left(SLastNameTextBox, SLastNameTextBox.TextLength - 1)
    End If
End Sub

Private Sub CompanyTextBox_Change()
    If CompanyTextBox.TextLength > 30 Then
    MsgBox "Please limit your input to 30 characters."
    CompanyTextBox.Text = Left(CompanyTextBox, CompanyTextBox.TextLength - 1)
    End If
End Sub

Private Sub SCompanyTextBox_Change()
    If SCompanyTextBox.TextLength > 30 Then
    MsgBox "Please limit your input to 30 characters."
    SCompanyTextBox.Text = Left(SCompanyTextBox, SCompanyTextBox.TextLength - 1)
    End If
End Sub
 
Upvote 0
leopardhawk said:
@wsnyder thanks Winston for the link but I still don't have the skills to integrate your code within mine so that it would work error free. I'm assuming that I would have to make quite a few changes and wouldn't even know where to begin... ?

I do appreciate you reaching out though!

Best!
Click to expand...
I agree when I see very complicated cod which is very long I try to stay away from it. I'm hoping JEC will come back and tell us how to do that JEC said:
It's pretty easy to reload some previous data into your userform while opening.

Now if your only wanting to reload textbox values that
leopardhawk said:
Still looking for some help if anyone knows how I might accomplish this. I am attaching the VBA code and an image of the UserForm if that will help. To recap, the UserForm is designed so that the user can enter data into specific sheets and cells throughout the workbook. When they click to OK button on this UserForm, it sends the data to different cells on Sheet11 of the workbook. Users may want to do this over long periods of time so there will be instances where the workbook is saved and closed.

What I would like to have happen is that when they reopen the workbook and launch the UserForm, any previously entered data will be displayed in the textboxes and or comboboxes that they had already filled in. All this of course IF it is possible.

Appreciate any help!


View attachment 49224

VBA Code: 
Private Sub CloseCommandButton_Click()

Unload Me

End Sub

Private Sub ComboBox4_Change()

End Sub

Private Sub GenderComboBox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   With Me.GenderComboBox
      If .Text = "M" Or .Text = "F" Or .Text = "" Then
         .BackColor = rgbWhite
         Label4.Caption = "Gender"
         Label4.ForeColor = rgbBlack
      Else
         Label4.Caption = "Please select M or F"
         Label4.ForeColor = RGB(255, 55, 55)
         .BackColor = RGB(255, 55, 55)
         GenderComboBox.Value = ""
      End If
   End With
End Sub

Private Sub SGenderComboBox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   With Me.SGenderComboBox
      If .Text = "M" Or .Text = "F" Or .Text = "" Then
         .BackColor = rgbWhite
         Label24.Caption = "Gender"
         Label24.ForeColor = rgbBlack
      Else
         Label24.Caption = "Please select M or F"
         Label24.ForeColor = RGB(255, 55, 55)
         .BackColor = RGB(255, 55, 55)
         SGenderComboBox.Value = ""
      End If
   End With
End Sub

Private Sub UserForm_Initialize()
Dim objControl As MSForms.Control

    For Each objControl In Me.Controls
        If TypeName(objControl) = "TextBox" And objControl.Tag <> "" Then
           Me.setupPlaceholder objControl.Name, False
        End If
    Next objControl

    MultiPage1.Value = 0
    Me.FirstNameTextBox.SetFocus
    Me.FirstNameTextBox.Value = Sheet11.Range("C9").Value
   
       
'Fill all 'Month' combo boxes with valid months
Dim MonthAry(1 To 12) As Variant, ComboAry As Variant
    Dim i As Long
  
        ComboAry = Array("B", "R", "C", "O", "SB", "SR", "SC", "SO")
          For i = 1 To 12
        MonthAry(i) = MonthName(i)
    Next i
        For i = 0 To UBound(ComboAry)
        Me.Controls(ComboAry(i) & "Month").List = MonthAry
    Next i
   
'Fill all 'Day' combo boxes with valid days
Dim Arry As Variant
   
    Arry = Evaluate("if({1},row(1:31))")
        Me.BDay.List = Arry
        Me.RDay.List = Arry
        Me.CDay.List = Arry
        Me.ODay.List = Arry
        Me.SBDay.List = Arry
        Me.SRDay.List = Arry
        Me.SCDay.List = Arry
        Me.SODay.List = Arry
   
'Fill all 'Year' combo boxes with valid years
Dim Ary As Variant
   
    Ary = Evaluate("if({1},row(1930:2130))")
        Me.BYear.List = Ary
        Me.RYear.List = Ary
        Me.CYear.List = Ary
        Me.OYear.List = Ary
        Me.SBYear.List = Ary
        Me.SRYear.List = Ary
        Me.SCYear.List = Ary
        Me.SOYear.List = Ary
   
'Fill 'Gender' combo box with valid genders
    Me.GenderComboBox.List = Array("M", "F")
    Me.GenderComboBox.Style = fmStyleDropDownCombo
   
'Force users to choose from combo boxes
        Me.BMonth.Style = 2
        Me.BDay.Style = 2
        Me.BYear.Style = 2
       
        Me.SBMonth.Style = 2
        Me.SBDay.Style = 2
        Me.SBYear.Style = 2
       
        Me.RMonth.Style = 2
        Me.RDay.Style = 2
        Me.RYear.Style = 2
       
        Me.SRMonth.Style = 2
        Me.SRDay.Style = 2
        Me.SRYear.Style = 2
       
        Me.CMonth.Style = 2
        Me.CDay.Style = 2
        Me.CYear.Style = 2
       
        Me.SCMonth.Style = 2
        Me.SCDay.Style = 2
        Me.SCYear.Style = 2
       
        Me.OMonth.Style = 2
        Me.ODay.Style = 2
        Me.OYear.Style = 2
       
        Me.SOMonth.Style = 2
        Me.SODay.Style = 2
        Me.SOYear.Style = 2
   
'Fill 'Pension Provider' combo boxes with list of Canadian pension providers
    Dim LastRow As Long
    Dim SheetName As String
    SheetName = "Sheet20"
    LastRow = Sheets(SheetName).Cells(Rows.Count, "A").End(xlUp).Row
    Me.ProviderComboBox.List = Sheets("Sheet20").Range("A2:A" & LastRow).Value
    Me.SProviderComboBox.List = Sheets("Sheet20").Range("A2:A" & LastRow).Value
   
'Fill 'Pension Option' combo boxes with list of popular pension options
    Me.OptionComboBox.List = Array("100% Joint Life", _
        "100% Joint Life 5-year guarantee", "100% Joint Life 10-year guarantee", "100% Joint Life 15-year guarantee", _
        "75% Joint Life 5-year guarantee", "75% Joint Life 10-year guarantee", "75% Joint Life 15-year guarantee", _
        "60% Joint Life 5-year guarantee", "60% Joint Life 10-year guarantee", "60% Joint Life 15-year guarantee", _
        "Single Life", _
        "Single Life 5-year guarantee", "Single Life 10-year guarantee", "Single Life 15-year guarantee", _
        "Other")

'Fill 'Spouse/Partner Gender' combo box with valid genders
    Me.SGenderComboBox.List = Array("M", "F")
    Me.SGenderComboBox.Style = fmStyleDropDownCombo
   
'Fill 'Spouse/Partner Pension Option' combo boxes with list of popular pension options
    Me.SOptionComboBox.List = Array("100% Joint Life", _
        "100% Joint Life 5-year guarantee", "100% Joint Life 10-year guarantee", "100% Joint Life 15-year guarantee", _
        "75% Joint Life 5-year guarantee", "75% Joint Life 10-year guarantee", "75% Joint Life 15-year guarantee", _
        "60% Joint Life 5-year guarantee", "60% Joint Life 10-year guarantee", "60% Joint Life 15-year guarantee", _
        "Single Life", _
        "Single Life 5-year guarantee", "Single Life 10-year guarantee", "Single Life 15-year guarantee", _
        "Other")

End Sub

'Set focus to first available box on each tab of the multi-page userForm
Private Sub MultiPage1_Change()

    GetTextBoxByTabIndex(Me.MultiPage1.SelectedItem, 0).SetFocus

End Sub

Private Function GetTextBoxByTabIndex(ByVal ControlParent As Object, ByVal TabIndex As Long) As Control

    Dim oCtrl As Control
   
    For Each oCtrl In ControlParent.Controls
        If TypeOf oCtrl Is MSForms.TextBox Then
            If oCtrl.TabIndex = TabIndex Then
                Set GetTextBoxByTabIndex = oCtrl
                Exit For
            End If
        End If
    Next oCtrl

End Function

Private Sub OKCommandButton_Click()

'Combine month, day, year into valid dates
    Dim DOB As Variant
        On Error Resume Next
        DOB = DateSerial(Me.BYear, Month(DateValue(Me.BMonth & "/01/2020")), Me.BDay)
        On Error GoTo 0
   
    Dim RD As Variant
        On Error Resume Next
        RD = DateSerial(Me.RYear, Month(DateValue(Me.RMonth & "/01/2020")), Me.RDay)
        On Error GoTo 0
                  
    Dim CSD As Variant
        On Error Resume Next
        CSD = DateSerial(Me.CYear, Month(DateValue(Me.CMonth & "/01/2020")), Me.CDay)
        On Error GoTo 0
                  
    Dim OSD As Variant
        On Error Resume Next
        OSD = DateSerial(Me.OYear, Month(DateValue(Me.OMonth & "/01/2020")), Me.ODay)
        On Error GoTo 0
       
    Dim SDOB As Variant
        On Error Resume Next
        SDOB = DateSerial(Me.SBYear, Month(DateValue(Me.SBMonth & "/01/2020")), Me.SBDay)
        On Error GoTo 0
       
    Dim SRD As Variant
        On Error Resume Next
        SRD = DateSerial(Me.SRYear, Month(DateValue(Me.SRMonth & "/01/2020")), Me.SRDay)
        On Error GoTo 0
                  
    Dim SCSD As Variant
        On Error Resume Next
        SCSD = DateSerial(Me.SCYear, Month(DateValue(Me.SCMonth & "/01/2020")), Me.SCDay)
        On Error GoTo 0
                  
    Dim SOSD As Variant
        On Error Resume Next
        SOSD = DateSerial(Me.SOYear, Month(DateValue(Me.SOMonth & "/01/2020")), Me.SODay)
        On Error GoTo 0
                  
    With Sheets(11)

'Send data to sheets
               
        If Not AllmostEmpty(FirstNameTextBox) Then .Range("C9").Value = FirstNameTextBox.Value
             
        If LastNameTextBox <> "Optional" Then
            If Not AllmostEmpty(LastNameTextBox) Then .Range("D9").Value = LastNameTextBox.Value
        End If
       
        If IsDate(DOB) Then .Range("E9").Value = DOB
        If Not AllmostEmpty(GenderComboBox) Then .Range("F9").Value = GenderComboBox.Value
       
        If CompanyTextBox <> "Optional" Then
            If Not AllmostEmpty(CompanyTextBox) Then .Range("G9").Value = CompanyTextBox.Value
        End If
       
        If IsDate(RD) Then .Range("C15").Value = RD
        If Not AllmostEmpty(OptionComboBox) Then .Range("D15").Value = OptionComboBox.Value
        If Not AllmostEmpty(ProviderComboBox) Then .Range("E15").Value = ProviderComboBox.Value
        If IsDate(CSD) Then .Range("F15").Value = CSD
        If IsDate(OSD) Then .Range("G15").Value = OSD
       
       
    End With
       
    With Sheets(11)

              
        If Not AllmostEmpty(SFirstNameTextBox) Then .Range("C11").Value = SFirstNameTextBox.Value
       
        If SLastNameTextBox <> "Optional" Then
            If Not AllmostEmpty(SLastNameTextBox) Then .Range("D11").Value = SLastNameTextBox.Value
        End If
       
        If IsDate(SDOB) Then .Range("E11").Value = SDOB
        If Not AllmostEmpty(SGenderComboBox) Then .Range("F11").Value = SGenderComboBox.Value
       
        If SCompanyTextBox <> "Optional" Then
            If Not AllmostEmpty(SCompanyTextBox) Then .Range("G11").Value = SCompanyTextBox.Value
        End If
       
        If IsDate(SRD) Then .Range("C17").Value = SRD
        If Not AllmostEmpty(SOptionComboBox) Then .Range("D17").Value = SOptionComboBox.Value
        If Not AllmostEmpty(SProviderComboBox) Then .Range("E17").Value = SProviderComboBox.Value
        If IsDate(SCSD) Then .Range("F17").Value = SCSD
        If IsDate(SOSD) Then .Range("G17").Value = SOSD
       
           
    End With
   
Unload Me
   
End Sub

'Set textbox tags to gray
Sub setupPlaceholder(txtBox As String, focus As Boolean)
    With Me.Controls(txtBox)
        If Len(.Text) = 0 And Not focus Then
            .Text = .Tag
            .ForeColor = vbGrayText
        ElseIf .Text = .Tag Then
            .Text = ""
            .ForeColor = vbWindowText
        End If
    End With
End Sub

Private Sub LastNameTextBox_Enter()
    setupPlaceholder LastNameTextBox.Name, True
End Sub

Private Sub LastNameTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder LastNameTextBox.Name, False
End Sub

Private Sub CompanyTextBox_Enter()
    setupPlaceholder CompanyTextBox.Name, True
End Sub

Private Sub CompanyTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder CompanyTextBox.Name, False
End Sub

Private Sub SLastNameTextBox_Enter()
    setupPlaceholder SLastNameTextBox.Name, True
End Sub

Private Sub SLastNameTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder SLastNameTextBox.Name, False
End Sub

Private Sub SCompanyTextBox_Enter()
    setupPlaceholder SCompanyTextBox.Name, True
End Sub

Private Sub SCompanyTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder SCompanyTextBox.Name, False
End Sub

'Limit number of characters in each textbox or combobox
Private Sub ProviderComboBox_Change()
    If ProviderComboBox.TextLength > 42 Then
    MsgBox "Please limit your input to 42 characters."
    ProviderComboBox.Text = Left(ProviderComboBox, ProviderComboBox.TextLength - 1)
    End If
End Sub

Private Sub SProviderComboBox_Change()
    If SProviderComboBox.TextLength > 42 Then
    MsgBox "Please limit your input to 42 characters."
    SProviderComboBox.Text = Left(SProviderComboBox, SProviderComboBox.TextLength - 1)
    End If
End Sub

Private Sub OptionComboBox_Change()
    If OptionComboBox.TextLength > 34 Then
    MsgBox "Please limit your input to 34 characters."
    OptionComboBox.Text = Left(OptionComboBox, OptionComboBox.TextLength - 1)
    End If
End Sub

Private Sub SOptionComboBox_Change()
    If SOptionComboBox.TextLength > 34 Then
    MsgBox "Please limit your input to 34 characters."
    SOptionComboBox.Text = Left(SOptionComboBox, SOptionComboBox.TextLength - 1)
    End If
End Sub

Private Sub FirstNameTextBox_Change()
    If FirstNameTextBox.TextLength > 13 Then
    MsgBox "Please limit your input to 13 characters."
    FirstNameTextBox.Text = Left(FirstNameTextBox, FirstNameTextBox.TextLength - 1)
    End If
End Sub

Private Sub SFirstNameTextBox_Change()
    If SFirstNameTextBox.TextLength > 13 Then
    MsgBox "Please limit your input to 13 characters."
    SFirstNameTextBox.Text = Left(SFirstNameTextBox, SFirstNameTextBox.TextLength - 1)
    End If
End Sub

Private Sub LastNameTextBox_Change()
    If LastNameTextBox.TextLength > 28 Then
    MsgBox "Please limit your input to 28 characters."
    LastNameTextBox.Text = Left(LastNameTextBox, LastNameTextBox.TextLength - 1)
    End If
End Sub

Private Sub SLastNameTextBox_Change()
    If SLastNameTextBox.TextLength > 28 Then
    MsgBox "Please limit your input to 28 characters."
    SLastNameTextBox.Text = Left(SLastNameTextBox, SLastNameTextBox.TextLength - 1)
    End If
End Sub

Private Sub CompanyTextBox_Change()
    If CompanyTextBox.TextLength > 30 Then
    MsgBox "Please limit your input to 30 characters."
    CompanyTextBox.Text = Left(CompanyTextBox, CompanyTextBox.TextLength - 1)
    End If
End Sub

Private Sub SCompanyTextBox_Change()
    If SCompanyTextBox.TextLength > 30 Then
    MsgBox "Please limit your input to 30 characters."
    SCompanyTextBox.Text = Left(SCompanyTextBox, SCompanyTextBox.TextLength - 1)
    End If
End Sub
Click to expand...
Sorry to see you have not received a answer. I was hoping JEC would come back with his answer.
 
Upvote 0
Well, I can give a simple example but I cant share files at MrExcel.. a picture with some code does not tell a lot..
 
Last edited by a moderator:
Upvote 0
Hi,
give this a try & see if it does what you want

Place following code In a STANDARD module

VBA Code: 
Sub ControlSettings(ByVal Form As Object, ByVal Action As Integer)
    Dim ws          As Worksheet
    Dim r           As Long
    Dim m           As Variant
    Dim ctrl        As Control
  
    'create sheet to store userform control values
    If Not Evaluate("ISREF('" & Form.Name & "'!A1)") Then
      
        Set ws = Worksheets.Add(After:=Worksheets(Sheets.Count))
            ws.Name = Form.Name
         
    Else
        'sheet exists
        Set ws = Worksheets(Form.Name)
    End If
  
    'hide sheet
     ws.Visible = xlSheetVeryHidden
   
     'loop controls
    For Each ctrl In Form.Controls
        Select Case TypeName(ctrl)
            'check only these controls only
            Case "TextBox", "ComboBox", "ListBox", "OptionButton", "CheckBox", "ToggleButton"
                'see if control name exists in list
                m = Application.Match(ctrl.Name, ws.Columns(1), 0)
                'get the row for control name
                r = IIf(IsError(m), ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1, CLng(m))
                With ws.Cells(r, 1)
                    'if missing, add name to list
                    If IsError(m) Then .Value = ctrl.Name
                    If Action = xlOpen Then
                        'return control value
                        ctrl.Value = IIf(VarType(ctrl) = vbBoolean And Len(.Offset(, 1).Value) = 0, False, .Offset(, 1).Value)
                    Else
                        'save control value
                        .Offset(, 1).Value = ctrl.Value
                    End If
                End With
        End Select
    Next
  
End Sub

and to use it from your userform(s) you need to include the lines of code shown in these two events

Rich (BB code): 
Private Sub UserForm_Initialize()
    ControlSettings Me, xlOpen
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ControlSettings Me, xlClosed
  'optional
   'ThisWorkBook.Save
End Sub

Solution should work for any number of userforms in your project BUT will only store / return the last users settings & the workbook is saved before closing.

Dave
 
Upvote 1
Solution
leopardhawk said:
@dmt32 thank you so much for your suggestion, It works really well...!!
Click to expand...

Most welcome & glad it does what you want

Solution can if needed, be updated if multiple users access the workbook on your network, to record their individual settings

Appreciate your feedback

Dave
 
Upvote 1
You must log in or register to reply here.

Similar threads

F
  • Question
Userform textbox launches another userform
Replies
8
Views
344
FedElecQaEng
F
P
  • Question
Send UserForm value to another UserFrom
Replies
5
Views
238
RoryA
RoryA
S
  • Solved
userform command button, list box, and check box macro
Replies
7
Views
305
dmt32
D
K
  • Solved
populating a textbox on a userform from selections that are made from a listbox that is one another userform (popup)...
Replies
4
Views
176
kbishop94
K
D
  • Question
Can i have active userform and worksheet, handled by user, at the same time?
Replies
1
Views
331
RoryA
RoryA

Forum statistics

Threads
1,214,644
Messages
6,120,709
Members
448,983
Latest member
Joaquim_Baptista

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top