CODEWITHSUNDEEP
vbaexceluserform
Vinay billa
Vinay billa

Reputation: 309

How to save the values in a userform in vba?

I have a userform which looks like this

enter image description here

All the textboxes are filled with default values once a user opens this form.

The user then changes the values manually and presses OK which will close the form and will affect the charts linked to the form.

If the user opens the form again, the values in the textboxes revert back to default ones and the charts linked will assume their default positions too.

Is there any way where once the user presses OK, the values in the userform gets saved so when the user opens the form the next time, they are presented with the changed values instead of the default ones??

Thanks!

Upvotes: 2

Views: 11578

Answers (2)

devbf
devbf

Reputation: 571

Another solution if you don´t have similiar names (but Hungarian Notion like cbTest for a combo box) for the control elements, so you cannot iterate through them with a command like Controls("TextBox" & i)....

If you have a userform (e. g. called ufTest), then you can iterate through all control elements like this:

    Dim controlElement as Control
    For Each controlElement In ufTest.Controls
        MsgBox controlElement.Name 
        ' Check for type of control
        If Left(controlElement.Name, 2) = "cb" Then
            MsgBox = controlElement.Text
        ElseIf Left(controlElement.Name, 3) = "txt" Then
            MsgBox = controlElement.Value
    End If
   Next controlElement

This gets you the name of every control element in your userform, no matter how you´ve called your controls.

P. S.: I´m sure there is a way to get the type of the control without using the Hungarian Notion, but I did not find it in time.

Upvotes: 0

Benno Grimm
Benno Grimm

Reputation: 540

Have you tried using the Sheet as a place to save the data?

Dim i As Integer
Private Sub UserForm_Initialize()
    For i = 1 To 49                 'Number of TextBoxes (I counted 49)
        Controls("TextBox" & i).Text = Cells(i, 1)
    Next i
End Sub

Private Sub CommandButton1_Click()  'Save button
    For i = 1 To 49
        Cells(i, 1) = Controls("TextBox" & i).Text
    Next i
End Sub

This will save the values in the first 49 rows of the first coloumn. If you want to save it to an other Sheet, create a new one and add Worksheets("SheetName").Cells....

Upvotes: 2

Related Questions