none
Excel VBA userform auto fill Textbox ffrom worksheet

    Question

  • I have created a Userform named “roster1” which contains a ComboBox named “Comweek1” with a list of values 1 to 10 and 2 Command buttons named “Comunita”& “ComunitB” , lastly 7 TextBox named “Textshift1” to “textshift7”.

    The form also contains a datepicker which populates Textbox1 to 7

    Private Sub Comunita_Click()

    TextBox1.Value = Format(date1.Value, "dd/mm/yyyy")

    TextBox2.Value = Format(date1.Value + 1, "dd/mm/yyyy")

    TextBox3.Value = Format(date1.Value + 2, "dd/mm/yyyy")

    TextBox4.Value = Format(date1.Value + 3, "dd/mm/yyyy")

    TextBox5.Value = Format(date1.Value + 4, "dd/mm/yyyy")

    TextBox6.Value = Format(date1.Value + 5, "dd/mm/yyyy")

    TextBox7.Value = Format(date1.Value + 6, "dd/mm/yyyy")

    End Sub

    I am working with a 10 week roster I am trying to get the Textboxes “textshift1” to 7 to fill with the weeks shifts based on the number selected in “Comweek1” and the Command Button clicked. If I can get it to work with Command Button “Comunita” I can amend the code for the second button.

    “Sheet1” contains the shifts information in a 7 day pattern .

    Cell A:3 = “A1” Cell B:3 = 1 Cell C:3 = “7am to 5pm”&&&&&& Cell i:3 = “Rest”

    Down to Cell B:12 = “10” to Cell i:12 = Rest

                       
                       

    A1

    1

    7am-5pm

    7am-5pm

    4pm-2am

    4pm-2am

    10pm-8am

    10pm-8am

    Rest

     
     

    2

    Rest

    Rest

    Rest

    7am-5pm

    7am-5pm

    9am-7pm

    3pm-11pm

     
     

    3

    4pm-2am

    4pm-2am

    Rest

    Rest

    Rest

    Rest

    7am-3pm

     
     

    4

    7am-5pm

    4pm-2am

    4pm-2am

    10pm-8am

    10pm-8am

    Rest

    Rest

     
     

    5

    Rest

    Rest

    7am-5pm

    7am-5pm

    9am-7pm

    9am-7pm

    3pm-11pm

     
     

    6

    4pm-2am

    Rest

    Rest

    Rest

    Rest

    7am-5pm

    7am-3pm

     
     

    7

    4pm-2am

    4pm-2am

    10pm-8am

    10pm-8am

    Rest

    Rest

    Rest

     
     

    8

    Rest

    7am-5pm

    7am-5pm

    9am-7pm

    9am-7pm

    4pm-2am

    4pm-2am

     
     

    9

    Rest

    Rest

    Rest

    Rest

    7am-5pm

    7am-5pm

    3pm-11pm

     
     

    10

    4pm-2am

    4pm-2am

    4pm-2am

    Rest

    Rest

    Rest

    Rest

     
                       
                       

    I am unable to figure out how to get them to populate based on the number selected in ComboBox named “Comweek1”in conjunction with clicking on the Command button named “Comunita”.

    Gerry VBA Novice

    Saturday, February 04, 2012 6:43 PM

Answers

  • Try the following code. I have used a loop which returns the offset value from the found shift number and also uses the loop value in a concatenation for the textbox name. I have attached the code to the command button click event as per your description but you could also place the code in the combo box change event so you don't need the command button and the code will automatically run each time the combo box value is changed.

     

    Private Sub ComunitA_Click()

        Dim rngToFind As Range
        Dim i As Long
       
        With Sheets("Sheet1").Range("B3:B12")   'Edit "Sheet1" if required
            Set rngToFind = .Find(What:=ComWeek1, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
        End With

         If Not rngToFind Is Nothing Then   'Not nothing means found
            For i = 1 To 7
                Me.Controls("textshift" & i) = rngToFind.Offset(0, i)
            Next i
        Else
            'MsgBox error routine not essential.
            MsgBox "Error! Value in combo box not found." & vbCrLf & _
                    "Processing terminated."
            Exit Sub
        End If
           
    End Sub

     


    Regards, OssieMac
    • Edited by OssieMac Saturday, February 04, 2012 11:12 PM
    • Marked as answer by bigger312 Sunday, February 05, 2012 8:52 PM
    Saturday, February 04, 2012 11:09 PM

All replies

  • Try the following code. I have used a loop which returns the offset value from the found shift number and also uses the loop value in a concatenation for the textbox name. I have attached the code to the command button click event as per your description but you could also place the code in the combo box change event so you don't need the command button and the code will automatically run each time the combo box value is changed.

     

    Private Sub ComunitA_Click()

        Dim rngToFind As Range
        Dim i As Long
       
        With Sheets("Sheet1").Range("B3:B12")   'Edit "Sheet1" if required
            Set rngToFind = .Find(What:=ComWeek1, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
        End With

         If Not rngToFind Is Nothing Then   'Not nothing means found
            For i = 1 To 7
                Me.Controls("textshift" & i) = rngToFind.Offset(0, i)
            Next i
        Else
            'MsgBox error routine not essential.
            MsgBox "Error! Value in combo box not found." & vbCrLf & _
                    "Processing terminated."
            Exit Sub
        End If
           
    End Sub

     


    Regards, OssieMac
    • Edited by OssieMac Saturday, February 04, 2012 11:12 PM
    • Marked as answer by bigger312 Sunday, February 05, 2012 8:52 PM
    Saturday, February 04, 2012 11:09 PM
  • Hi

    Thanks for your help. Code working perfectly untill I select a value above 5

    6 to 10 return an error as per your MsgBox  

    Error! Value in combo box not found.
                    "Processing terminated."

    Thanks

    Gerry

    VBA Novice

    Ireland

    Sunday, February 05, 2012 1:51 PM
  • I tested the code on a userform that I created as close as possible to your description but it is very possible that I have mis-interpretted the information provided and my emulation is not correct.

    The only things I can suggest are:

    The range being searched does not have the numbers 6 to 10 included. Check that the range being searched, Sheets("Sheet1").Range("B3:B12"), is correct and does contain the shift numbers 1 to 10.

    I am now wondering if you are using 2 lines for each shift number and the range for the shift numbers is really B3:B21. If this is so, then are the shifts like 7am-5pm also on 2 lines. I interpreted the example as they were on a single line with wrapped text.

    If not the above. The numbers in the range being searched or the row source for the combo box are corrupt. By this I mean the numbers have been entered as text and have either leading or trailing spaces.

     


    Regards, OssieMac
    Sunday, February 05, 2012 8:23 PM
  • I replaced the rowsource for "Comweek1" with

    Private Sub UserForm_Initialize()
        With Me.Comweek1
        .AddItem "1"
        .AddItem "2"
        .AddItem "3"
        .AddItem "4"
        .AddItem "5"
        .AddItem "6"
        .AddItem "7"
        .AddItem "8"
        .AddItem "9"
        .AddItem "10"
       
        End With
    End Sub

     

    and your code works perfectly.

    Thanks for all your help.

    Gerry

     

    Sunday, February 05, 2012 9:03 PM