assigning value to dynamically built textbox name using excel vba

Question

I am using monthview control as calander in separate userform called dateform.

I have multiple date fields on excel vba userform called Update_Entries_New hence I cannot directly reference date clicked on monthview calander like below (though direct referencing like below works perfectly fine).

Update_Entries_New.Invoice_date.Value = DateClicked

To solve the issue, I am generating Update_Entries_New.Invoice_date.Value dynamically to access monthview calander in all date fields in all userforms I have in project.

When I use dynamically created textbox name (picked from excel sheet cell) date clicked on monthview calander doesn't gets updated on textbox value from where calander got populated. any clue why this is happening.

my current code

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
    Dim test
       'dynamically referencing of "Update_Entries_New.Invoice_date.Value" picked from 'Sheets("Data").Range("m2").Value' doesn't update the value on userform textbox
       controlname = Sheets("Data").Range("m2").Value
       controlname = DateClicked

       'using textbox name directly works fine with below code
       Update_Entries_New.Invoice_date.Value = DateClicked

      Unload dateform
End Sub

That's cool I was busy updating another post. You can refer to the newly added controls using their name without iterating over the collection (e.g. Update_Entries_New.Controls("Invoice_date").Value = DateClicked ). — user6432984
You should post your solution and accept it as the answer to close your post. — user6432984

techworld techworld · Accepted Answer · 2016-10-06T05:58:25

i got this working using following code:

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
    test = Sheets("Data").Range("m1").Value
    CB = Sheets("Data").Range("m2").Value
    CBVal = DateClicked

    If test = "Update_Entries_New" Then
        For Each contr In Update_Entries_New.Controls
                If contr.Name = CB Then
                    contr.Value = CBVal
                End If
        Next
    End If

    Unload dateform
End Sub

assigning value to dynamically built textbox name using excel vba

1 Answers