Populate textbox on userform using variable string name...

[feetimo]

All-
I have a userform called frmParameters that allows users of an Excel worksheet to enter three parameters that Excel uses in calculations within the worksheet. Currently, another procedure successfully is saving these parameters into cell P2 on the active worksheet. An example of the saved string:

Parameters:txtSRWraps:4.33;txtSSWraps:3.33;txtDCCWraps:2.33;

The procedure below executes when the userform is opened--if P2 does not contain parameters already, it will set them to a default (this hasn't been written yet)...but...if Parameters are in P2, I want to loop though the string and read the parameters that were saved into the textboxes.

Here's the trick...I want to allow the userform to be easily expanded in the future, so when the userform WRITES to cell P2, it saves the textbox name (Like "txtSRWraps" in the above example), then ":" to seperate, then the textbox value (like "4.33" in the above example), then ";" to seperate, and the next textbox name. This is written in the other procedure to cycle through ALL the textboxes on the form so all are saved (even if new ones are added, without having to add more code).

Now, I want to read the values in from "P2", and the textbox names and values are specified within the string. I get a Runtime Error '13': Type Mismatch when the code hits the "Set ObjTB".... Help! How do I populate the textboxes?

Private Sub UserForm_Initialize()
'Copy any existing Parameters to the Form as Defaults
Dim ParametersDoNotExist As Boolean

ParametersDoNotExist = InStr(ActiveSheet.Range("P2").Value, "Parameters") = 0 Or IsNull(InStr(ActiveSheet.Range("P2").Value, "Parameters"))
   If ParametersDoNotExist Then
     MsgBox ("Need to write procedure to use default parameters.")
     ' Call SetParametersToDefaults               'Need to write this Procedure still.
   Else
     MsgBox ("Existing parameters Found.")
      '----------Get Parameters from cell where they are stored.----------
      Dim objTB As TextBox
      Dim StartPosition As Double
      Dim EndPosition As Double
      Dim TransferParameter As String
      Dim TransferParamValue As String
      Dim SearchString As String
      Dim EndOfString As Boolean

      'Initialize variables
      TransferParameter = ""
      TransferParamValue = ""
      SearchString = ""
      StartPosition = 1
      EndPosition = 1

       
      SearchString = ActiveSheet.Range("P2").Value
       
      Do Until EndOfString = True
         StartPosition = InStr(StartPosition, SearchString, "txt") + Len(TransferParameter)
         EndPosition = InStr(StartPosition, SearchString, ":")
         TransferParameter = Mid(SearchString, StartPosition, (EndPosition - StartPosition))
         StartPosition = EndPosition   ' Reset Start of Search to the end of the Parameter Value
         EndPosition = InStr(StartPosition, SearchString, ";")
         TransferParamValue = Mid(SearchString, StartPosition + 1, (EndPosition - (StartPosition + 1)))
 Stop
         MsgBox ("Found Parameter: " & TransferParameter & " " & TransferParamValue)
         Set objTB = Me.Controls(TransferParameter)
         'objTB.Text = TransferParamValue
         MsgBox ("Found Parameter: " & TransferParameter & " " & TransferParamValue)
         
         EndOfString = False    'Will need a way to determine if we are at the end of the string and end the loop if we are.
              
       Loop

 End If


'Start user at the top
Me.txtSRWraps.SetFocus

End Sub

-Tim

 

[AlphaFrog]

You have to Dim objTB As Control or Object and not as a TextBox. objTB has to be a generic Control or Object if you want to use Me.Controls("???") to assign it to a control.

On another note, the code below uses the Split function to parse your text string SearchString .

Private Sub UserForm_Initialize()
'Copy any existing Parameters to the Form as Defaults
Dim ParametersDoNotExist As Boolean

ParametersDoNotExist = InStr(ActiveSheet.Range("P2").Value, "Parameters") = 0 Or IsNull(InStr(ActiveSheet.Range("P2").Value, "Parameters"))
   If ParametersDoNotExist Then
     MsgBox ("Need to write procedure to use default parameters.")
     ' Call SetParametersToDefaults               'Need to write this Procedure still.
   Else
     MsgBox ("Existing parameters Found.")
      '----------Get Parameters from cell where they are stored.----------
      Dim objTB As Control
      Dim a As Variant, b As Variant, i As Long

      SearchString = ActiveSheet.Range("P2").Value
      'Strip leader "Parameters:" and trailing semicolon
      SearchString = Mid(SearchString, 12)
      If Right(SearchString, 1) = ";" Then SearchString = Left(SearchString, Len(SearchString) - 1)
      
      a = Split(SearchString, ";")
      For i = 0 To UBound(a)
        b = Split(a(i), ":")
        'MsgBox ("Found Parameter: " & b(0) & " " & b(1))
        Me.Controls(b(0)).Text = b(1)
      Next i
       
 End If


'Start user at the top
'Me.txtSRWraps.SetFocus

End Sub

Why wouldn't you use individual cells for each TextBox setting?

 

[feetimo]

Thanks for the help...I'll try it later today and post the results. I saved the results in one "hidden" cell to try to keep the users from editing the cells directly, which could lead to malfunctions in the formulas that will use the default values...

-Tim

 

[Norie]

Couldn't you store the data in a listbox instead of multiple textboxes?

When a value needs to be changed the user selects it in the listbox and they can enter the new value in a textbox.

They could also add and delete parameters.

Just an idea.

Here's the code to populate the listbox anyway.

Private Sub UserForm_Initialize()
Dim strParams As String
Dim arrParams
Dim I As Long
Dim pos As Long
 
    strParams = Replace(Range("P2"), "Parameters:", "")
    
    arrParams = Split(strParams, ";")
    
    lstParams.ColumnCount = 2
    
    For I = LBound(arrParams) To UBound(arrParams)
    
        pos = InStr(arrParams(I), ":")
        
        If pos <> 0 Then

            lstParams.AddItem Left(arrParams(I), pos - 1)

            lstParams.List(lstParams.ListCount - 1, 1) = Mid(arrParams(I), pos + 1)

        End If
        
    Next I
        
End Sub

 

[AlphaFrog]

Thanks for the help...I'll try it later today and post the results. I saved the results in one "hidden" cell to try to keep the users from editing the cells directly, which could lead to malfunctions in the formulas that will use the default values...

You're welcome.

You could save the settings on a Very Hidden worksheet. The user wouldn't know that worksheet existed. VBA can reference the Very Hidden sheet the same as any other sheet.

Sheets("Settings").Visible = xlVeryHidden

 

[feetimo]

AlphaFrog-

It worked like a dream...fewer variables and a little cleaner code...thanks! For everyone's knowledge, the reason I save the parameters in a cell instead of a hidden (or "very hidden" worksheet) is that I want the parameters to be able to be different for each worksheet (each worksheet is used as a revision in my application)--and the user needs to be free to set them differently for each.

We usually copy the sheet we want to work from to a new sheet, then edit from there, so the parameters in P2 will also be copied over (though without the user's knowledge).

Question: There's not a "very hidden" for columns like there is for sheets??? Though the skipped column letter would be a give-away.



Norie-

I'll give your idea a shot before I go too far programming in the "use" of the parameters that are saved in cell P2 in case I want to go the listbox method. Thank you, too.


-Tim