VBA userform - Dynamic textbox default value

Question

I have two TextBox in my userform. One is for entering the Name, and the other one is for entering the income.

Now I create a modeless userform, so that user can keep insert the data

Suppose I already have list of Name. For example: Marry, Jamie, Michael

Is it posible to set this list as a default value for Name TextBox? For example:

After click the button, the userform Pop up and Shows as follow:

Name: Marry
Income: ""

After I enter the income, and click "OK" buttom, the userform will Pop up again. This time it Shows like this:

Name: Jamie
Income: ""

If my question is not clear enough, please tell me and I will explain it more detailed. Thanks in advance.

Update:

I add my code here to make my question clearer. However, the "story" of my code is a Little bit different. The user will insert the Portfolio ID, Budget value and the date into userform. Then the macro will filter the table in sheet "ALL_List".

Based on Portfolio ID and date, there will be only one line data in that table after filtering. The Budget column for this line of data should be empty. The Macro should automatically insert the Budget value, which was recorded in userform, into Budget column.

There are, for example, 5 ID and 5 Budget value:

Date / ID / Budget

29/06/2018 / 12541 / 336521

29/06/2018 / 4521 / 658882

29/06/2018 / 44359 / 4587996

29/06/2018 / 10223 / 148665

29/06/2018 / 74 / 658324

So, when the first time userform Pop up. I hope there will be a Default ID value "12541" in the Portfolio ID TextBox. After I enter the date and Budget value and click the button "Enter", the Budget value will insert to the Budget column in sheet "ALL_List". Then the userform Pop up again. This time the Default value for ID will be 4521.

After the final Default ID (74) Show up and I enter the value and click Enter, I hope the userform will still Pop up and this time the value of Portfolio ID TextBox will be empty (because there could be other ID which the user wants to insert.)

Hope my descripition is clear. If there is any question, please don't hesitate to inform me. Much Thanks!

Sub Budget_Adjustment()

    Dim frm As New UserFormBudget
    frm.Show vbModeless

End Sub



Private Sub ButtonClose_Click()
    Unload Me
End Sub



Private Sub ButtonEnter_Click()
    InsertBudget
End Sub



Private Sub InsertBudget()

Dim UpdateDate As String
Dim PortfolioID, Budgetvalue As Long

    UpdateDate = TextBoxDate.Value
    PortfolioID = TextBoxID.Value
    Budgetvalue = TextBoxBedget.Value

    UpdateDate = CDate(UpdateDate)

    Sheets("ALL_List").Activate
    ActiveSheet.AutoFilterMode = False
    Range(Cells(1, 1), Cells(Cells(Rows.Count, 7).End(xlUp).row, 7)).AutoFilter Field:=1, Criteria1:=UpdateDate
    Range(Cells(1, 1), Cells(Cells(Rows.Count, 7).End(xlUp).row, 7)).AutoFilter Field:=3, Criteria1:=PortfolioID

    Cells(Cells(Rows.Count, "A").End(xlUp).row, "F").Value = Budgetvalue

    ActiveSheet.AutoFilterMode = False
    TextBoxID.Value = ""
    TextBoxBedget.Value = ""
    TextBoxID.SetFocus
End Sub

Private Sub TextBoxBedget_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        ButtonEnter_Click
    End If
End Sub



Private Sub UserForm_Activate()

    'Empty TextBoxID
     TextBoxID.Value = ""

    'Empty TextBoxBedget
     TextBoxBedget.Value = ""

     'Empty TextBoxDate
     TextBoxDate.Value = ""

     'Set Focus on NameTextBox
     TextBoxDate.SetFocus

End Sub

You could look at public variables and have an long variable that is incremented each time the form opens or the ok is clicked, this looks into a public array/list as it's index. Look at arrays and public variables — Nathan_Sav
– Nathan_Sav, Commented Aug 28, 2018 at 13:42

Kubie · Accepted Answer · 2018-08-30 21:44:25Z

1

...

EDIT:

Edited your code a little bit based on the new information you provided. Now, you just enter your ID's you want to edit before hand in the sheet named "list".

I added sheet named "List" :

This code goes in area when you right-click UserFormBudget > View Code:

Private Sub ButtonClose_Click()

    Dim lastListRow As Long

    With ThisWorkbook.Worksheets("List")
        lastListRow = .Cells(.Rows.Count, 1).End(xlUp).row
        .Range("A4:A" & lastListRow).Interior.ColorIndex = 0
    End With

    Unload Me

End Sub

Private Sub ButtonEnter_Click()

    InsertBudget

End Sub

Private Sub InsertBudget()

    Dim UpdateDate As String
    Dim PortfolioID As Long
    Dim Budgetvalue As Long
    Dim lastListRow As Long
    Dim row As Long

    UpdateDate = TextBoxDate.Value
    PortfolioID = TextBoxID.Value
    Budgetvalue = TextBoxBedget.Value

    If Len(UpdateDate) > 0 Then
        UpdateDate = CDate(UpdateDate)
    Else
        MsgBox "Need to enter a date"
        Exit Sub
    End If

    With Worksheets("ALL_List")
        .Activate
        .AutoFilterMode = False
        .Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count, 7).End(xlUp).row, 7)).AutoFilter Field:=1, Criteria1:=UpdateDate
        .Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count, 7).End(xlUp).row, 7)).AutoFilter Field:=3, Criteria1:=PortfolioID
        .Cells(.Cells(.Rows.Count, "A").End(xlUp).row, "F").Value = Budgetvalue
        .AutoFilterMode = False
    End With

    With ThisWorkbook.Worksheets("List")
        lastListRow = .Cells(.Rows.Count, 1).End(xlUp).row

        TextBoxID.Value = ""
        For row = 5 To lastListRow
            If .Cells(row, "A").Interior.Color <> RGB(255, 255, 0) Then
                TextBoxID.Value = .Cells(row, "A").Value
                .Cells(row, "A").Interior.Color = RGB(255, 255, 0)
                Exit For
            End If
            If row = lastListRow Then
                TextBoxDate.Value = ""
            End If
        Next
    End With

    TextBoxBedget.Value = ""
    TextBoxID.SetFocus

End Sub

Private Sub TextBoxBedget_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    If KeyCode = 13 Then
        ButtonEnter_Click
    End If

End Sub

And add this code in a module, so right-click project and insert new module, then paste:

Sub Budget_Adjustment()

    Dim frm As New UserFormBudget
    Dim lastListRow As Long

    With ThisWorkbook.Worksheets("List")
        lastListRow = .Cells(.Rows.Count, 1).End(xlUp).row

        If lastListRow = 3 Then
            frm.TextBoxDate.Value = ""
            frm.TextBoxID.Value = ""
            frm.TextBoxBedget.Value = ""
        Else
            frm.TextBoxID.Value = .Cells(4, "A").Value
            frm.TextBoxBedget.Value = .Cells(4, "B").Value
            .Cells(4, "A").Interior.Color = RGB(255, 255, 0)
        End If
    End With

    frm.TextBoxID.SetFocus
    frm.Show vbModeless


End Sub

Now, just right-click on the button on List sheet and assign it the macro Budget_Adjustment

edited Aug 30, 2018 at 21:44

answered Aug 28, 2018 at 14:08

Kubie

1,5714 gold badges13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

codingsnake99 Over a year ago

Hey Kubie, Thanks for your quick response! I tried your code, but the Problem is that: In the first time, the Default "Michael" truly shows in the Name box. However, after I click "ok" button, the userform Pop up again, but this time there is no default value "Jamie" in the Name box. Do you know why?

codingsnake99 Over a year ago

By the way, in my "ok" button, I also write some codes which can allow the value in Name and Income box to insert to other cells. I am not sure whether this will affect your codes.

Kubie Over a year ago

@Hao it depends on where your main list of names is stored and if it gets affected by your code

Kubie Over a year ago

@Hao you can edit your original answer and add your whole code and screenshot of sheet if you want me to look at it

codingsnake99 Over a year ago

Thank you so much. I tried your code on my Project and it works really well! Super appreciate your help!

|

Collectives™ on Stack Overflow

VBA userform - Dynamic textbox default value

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related