Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: SetFocus on Multi Page UserForm

  1. #1
    Join Date
    26th May 2006
    Posts
    77

    SetFocus on Multi Page UserForm

    Hi All,

    I've got a userform with multiple pages, and in my userform initialize sub I set the focus to "combobox1" on "page1".
    When a user is on page2 of the form and clicks my "ClearData" button, which calls the initialize event, I get a run time error 2110 - "Can't move focus... etc"

    I would like users to have the ability to clear the form and start over no matter what page they are on.

    Thanks if you can help

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,541

    Re: SetFocus on Multi Page UserForm

    Steve

    Can you post your code and/or attach a sample workbook?

    You really shouldn't need to use SetFocus if you set the form up correctly in the first place.

    Also there are better ways to reset a form.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th May 2006
    Posts
    77

    Re: SetFocus on Multi Page UserForm

    Thanks for the reply norie,

    Well the books way too big to post, and your probably right.. I doubt that I set the form up correctly, so I won't bore you with the code, its gotta be 600 lines by now. Thats because I'm just getting into the whole "VBA thing" for work.

    I did one simple little userform and now 14 bosses want a bunch of "cool" stuff. I'm getting through it though and learning alot.

    I solved my immediate problem by adding the line
    VB:
    MultiPage1.Value = 0 
    
    
    to my initialize event and it seems to be working OK.

    Thanks for your help though.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2004
    Posts
    10,541

    Re: SetFocus on Multi Page UserForm

    Steve

    600 lines of code?

    What for?

    Whenever I see mention of lots of lines of code I get thinking that there's some redundancy/repetition going on.

    Mind you there is the old saying, if it ain't broke...

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    26th May 2006
    Posts
    77

    Re: SetFocus on Multi Page UserForm

    OK,

    I've decided that the worst that can happen is you'll get a good laugh. Just don't be drinking anything when you look at this, or you'll have it all over your screen :)

    And I figure if you have a few comments, then maybe I can learn some more...

    VB:
     '==============================================================
     'Better Roads Inc.  - Plant Production User Form - 6-15-06
     '==============================================================
    Dim rgData As Range 
    Dim vaData As Variant 
     
    Private m_blnCancel As Boolean 
     
    Private Sub cmdCancel_Enter() 
         ' flag when button is entered
        m_blnCancel = True 
    End Sub 
    Private Sub cmdCancel_Exit(ByVal Cancel As MSForms.ReturnBoolean) 
        m_blnCancel = False 
    End Sub 
    Private Sub cmdCancel_Click() 
        If MsgBox(" Cancelling Will Clear This Form." & vbNewLine & "      No Data Will Be Entered." & vbNewLine & "Are You Sure You Wish To Cancel?", vbYesNo + vbQuestion, "Cancel Data Entry") = vbNo Then 
            m_blnCancel = False 
            Exit Sub 
        End If 
        Unload Me 
    End Sub 
     'Private Sub cmdCancel_Click()
     '    If MsgBox(" Cancelling Will Clear This Form." & vbNewLine & "      No Data Will Be Entered." & vbNewLine & "Are You Sure You Wish To Cancel?", vbYesNo + vbQuestion, "Cancel Data Entry") = vbNo Then Exit Sub
     '    Unload Me
     'End Sub
     
    Private Sub cmdClearForm_Click() 
        If MsgBox("Are You Sure You Want To Clear This Form?", vbYesNo + vbQuestion, "Clear Form") = vbNo Then Exit Sub 
        Call UserForm_Initialize 
         
    End Sub 
     
    Private Sub CmdOK_Click() 
        Dim ma As String 
        Dim mb As String 
        Dim mc As String 
        ma = lblMixA.Caption 
        mb = lblMixB.Caption 
        mc = lblMixC.Caption 
        If MsgBox("          Better Roads Inc." & vbNewLine & "  Mix #1 Components Total " & ma & ".  " & vbNewLine & "  Mix #2 Components Total " & mb & ".  " & vbNewLine & "  Mix #3 Components Total " & mc & ".  " & vbNewLine & "Are You Sure This Form Is Acurate?     ", vbQuestion + vbYesNo, "Confirm Data Entry") = vbNo Then Exit Sub 
        Unload Me 
         
         
        With ActiveWorkbook.Sheets("Plant Production").Cells(65536, 1).End(xlUp).Offset(1, 0) 
            .Offset(0, 0) = txtDate.Value 
            .Offset(0, 84) = txtTime.Value 
            .Offset(0, 1) = cmbPlant.Value 
            .Offset(0, 2) = txtTonsDel.Value 
            .Offset(0, 3) = lblPlantProd.Caption 
            .Offset(0, 4) = txtRunTime.Value 
            .Offset(0, 5) = txtACTons.Value 
            .Offset(0, 6) = txt5Beg.Value 
            .Offset(0, 7) = txt5End.Value 
            .Offset(0, 8) = lbl5Tot.Caption 
            .Offset(0, 9) = txt2Beg.Value 
            .Offset(0, 10) = txt2End.Value 
            .Offset(0, 11) = lbl2Tot.Caption 
            .Offset(0, 12) = cmbMixA.Value 
            .Offset(0, 13) = txtMixA1.Value 
            .Offset(0, 14) = txtMixA2.Value 
            .Offset(0, 15) = txtMixA3.Value 
            .Offset(0, 16) = txtMixA4.Value 
            .Offset(0, 17) = txtMixA5.Value 
            .Offset(0, 18) = txtMixA6.Value 
            .Offset(0, 19) = txtMixA7.Value 
            .Offset(0, 20) = txtMixAQ.Value 
            .Offset(0, 21) = cmbMixB.Value 
            .Offset(0, 22) = txtMixB1.Value 
            .Offset(0, 23) = txtMixB2.Value 
            .Offset(0, 24) = txtMixB3.Value 
            .Offset(0, 25) = txtMixB4.Value 
            .Offset(0, 26) = txtMixB5.Value 
            .Offset(0, 27) = txtMixB6.Value 
            .Offset(0, 28) = txtMixB7.Value 
            .Offset(0, 29) = txtMixBQ.Value 
            .Offset(0, 30) = cmbMixC.Value 
            .Offset(0, 31) = txtMixC1.Value 
            .Offset(0, 32) = txtMixC2.Value 
            .Offset(0, 33) = txtMixC3.Value 
            .Offset(0, 34) = txtMixC4.Value 
            .Offset(0, 35) = txtMixC5.Value 
            .Offset(0, 36) = txtMixC6.Value 
            .Offset(0, 37) = txtMixC7.Value 
            .Offset(0, 38) = txtMixCQ.Value 
            .Offset(0, 39) = cmbWeatherAM.Value 
            .Offset(0, 40) = cmbWeatherPM.Value 
            .Offset(0, 41) = cmbWaste1.Value 
            .Offset(0, 42) = txtWaste1A.Value 
            .Offset(0, 43) = txtWaste1B.Value 
            .Offset(0, 44) = cmbWaste2.Value 
            .Offset(0, 45) = txtWaste2A.Value 
            .Offset(0, 46) = txtWaste2B.Value 
            .Offset(0, 47) = cmbWaste3.Value 
            .Offset(0, 48) = txtWaste3A.Value 
            .Offset(0, 49) = txtWaste3B.Value 
            .Offset(0, 50) = cmbWaste4.Value 
            .Offset(0, 51) = txtWaste4A.Value 
            .Offset(0, 52) = txtWaste4B.Value 
            .Offset(0, 53) = cmbEmp1.Value 
            .Offset(0, 54) = txtCrew1.Value 
            .Offset(0, 55) = txtHour1.Value 
            .Offset(0, 56) = cmbEmp2.Value 
            .Offset(0, 57) = txtCrew2.Value 
            .Offset(0, 58) = txtHour2.Value 
            .Offset(0, 59) = cmbEmp3.Value 
            .Offset(0, 60) = txtCrew3.Value 
            .Offset(0, 61) = txtHour3.Value 
            .Offset(0, 62) = cmbEmp4.Value 
            .Offset(0, 63) = txtCrew4.Value 
            .Offset(0, 64) = txtHour4.Value 
            .Offset(0, 65) = cmbEmp5.Value 
            .Offset(0, 66) = txtCrew5.Value 
            .Offset(0, 67) = txtHour5.Value 
            .Offset(0, 68) = txtSup1.Value 
            .Offset(0, 69) = txtLATons1.Value 
            .Offset(0, 70) = txtHauler1.Value 
            .Offset(0, 71) = txtOrdered1.Value 
            .Offset(0, 72) = txtDeliv1.Value 
            .Offset(0, 73) = txtSup2.Value 
            .Offset(0, 74) = txtLATons2.Value 
            .Offset(0, 75) = txtHauler2.Value 
            .Offset(0, 76) = txtOrdered2.Value 
            .Offset(0, 77) = txtDeliv2.Value 
            .Offset(0, 78) = txtSup3.Value 
            .Offset(0, 79) = txtLATons3.Value 
            .Offset(0, 80) = txtHauler3.Value 
            .Offset(0, 81) = txtOrdered3.Value 
            .Offset(0, 82) = txtDeliv3.Value 
            .Offset(0, 83) = txtNotes.Value 
             
             
             
             
             
             
        End With 
         
        With ActiveWorkbook.Sheets("PlantsCom").Cells(65536, 1).End(xlUp).Offset(1, 0) 
             'Mix A
            .Offset(0, 36) = txtDate.Value 
            .Offset(0, 37) = txtTime.Value 
            .Offset(0, 2) = cmbPlant.Value 
            .Offset(0, 0) = cmbMixA.Value 
            .Offset(0, 10) = txtMixA1.Value 
            .Offset(0, 15) = txtMixA2.Value 
            .Offset(0, 20) = txtMixA3.Value 
            .Offset(0, 25) = txtMixA4.Value 
            .Offset(0, 30) = txtMixA5.Value 
            .Offset(0, 35) = txtMixA6.Value 
            .Offset(0, 5) = txtMixA7.Value 
            .Offset(0, 1) = TextBox55.Value 
            .Offset(0, 3) = TextBox56.Value 
            .Offset(0, 4) = TextBox57.Value 
            .Offset(0, 6) = TextBox58.Value 
            .Offset(0, 7) = TextBox59.Value 
            .Offset(0, 8) = TextBox60.Value 
            .Offset(0, 9) = TextBox61.Value 
            .Offset(0, 11) = TextBox62.Value 
            .Offset(0, 12) = TextBox63.Value 
            .Offset(0, 13) = TextBox64.Value 
            .Offset(0, 14) = TextBox65.Value 
            .Offset(0, 16) = TextBox66.Value 
            .Offset(0, 17) = TextBox67.Value 
            .Offset(0, 18) = TextBox68.Value 
            .Offset(0, 19) = TextBox69.Value 
            .Offset(0, 21) = TextBox70.Value 
            .Offset(0, 22) = TextBox71.Value 
            .Offset(0, 23) = TextBox72.Value 
            .Offset(0, 24) = TextBox73.Value 
            .Offset(0, 26) = TextBox74.Value 
            .Offset(0, 27) = TextBox75.Value 
            .Offset(0, 28) = TextBox76.Value 
            .Offset(0, 29) = TextBox77.Value 
            .Offset(0, 31) = TextBox78.Value 
            .Offset(0, 32) = TextBox79.Value 
            .Offset(0, 33) = TextBox80.Value 
            .Offset(0, 34) = TextBox81.Value 
             
             'Mix B
            .Offset(1, 36) = txtDate.Value 
            .Offset(1, 37) = txtTime.Value 
            .Offset(1, 2) = cmbPlant.Value 
            .Offset(1, 0) = cmbMixB.Value 
            .Offset(1, 10) = txtMixB1.Value 
            .Offset(1, 15) = txtMixB2.Value 
            .Offset(1, 20) = txtMixB3.Value 
            .Offset(1, 25) = txtMixB4.Value 
            .Offset(1, 30) = txtMixB5.Value 
            .Offset(1, 35) = txtMixB6.Value 
            .Offset(1, 5) = txtMixB7.Value 
            .Offset(1, 1) = TextBox82.Value 
            .Offset(1, 3) = TextBox83.Value 
            .Offset(1, 4) = TextBox84.Value 
            .Offset(1, 6) = TextBox85.Value 
            .Offset(1, 7) = TextBox86.Value 
            .Offset(1, 8) = TextBox87.Value 
            .Offset(1, 9) = TextBox88.Value 
            .Offset(1, 11) = TextBox89.Value 
            .Offset(1, 12) = TextBox90.Value 
            .Offset(1, 13) = TextBox91.Value 
            .Offset(1, 14) = TextBox92.Value 
            .Offset(1, 16) = TextBox93.Value 
            .Offset(1, 17) = TextBox94.Value 
            .Offset(1, 18) = TextBox95.Value 
            .Offset(1, 19) = TextBox96.Value 
            .Offset(1, 21) = TextBox97.Value 
            .Offset(1, 22) = TextBox98.Value 
            .Offset(1, 23) = TextBox99.Value 
            .Offset(1, 24) = TextBox100.Value 
            .Offset(1, 26) = TextBox101.Value 
            .Offset(1, 27) = TextBox102.Value 
            .Offset(1, 28) = TextBox103.Value 
            .Offset(1, 29) = TextBox104.Value 
            .Offset(1, 31) = TextBox105.Value 
            .Offset(1, 32) = TextBox106.Value 
            .Offset(1, 33) = TextBox107.Value 
            .Offset(1, 34) = TextBox108.Value 
             
             'Mix C
            .Offset(2, 36) = txtDate.Value 
            .Offset(2, 37) = txtTime.Value 
            .Offset(2, 2) = cmbPlant.Value 
            .Offset(2, 0) = cmbMixC.Value 
            .Offset(2, 10) = txtMixC1.Value 
            .Offset(2, 15) = txtMixC2.Value 
            .Offset(2, 20) = txtMixC3.Value 
            .Offset(2, 25) = txtMixC4.Value 
            .Offset(2, 30) = txtMixC5.Value 
            .Offset(2, 35) = txtMixC6.Value 
            .Offset(2, 5) = txtMixC7.Value 
            .Offset(2, 1) = TextBox109.Value 
            .Offset(2, 3) = TextBox110.Value 
            .Offset(2, 4) = TextBox111.Value 
            .Offset(2, 6) = TextBox112.Value 
            .Offset(2, 7) = TextBox113.Value 
            .Offset(2, 8) = TextBox114.Value 
            .Offset(2, 9) = TextBox115.Value 
            .Offset(2, 11) = TextBox116.Value 
            .Offset(2, 12) = TextBox117.Value 
            .Offset(2, 13) = TextBox118.Value 
            .Offset(2, 14) = TextBox119.Value 
            .Offset(2, 16) = TextBox120.Value 
            .Offset(2, 17) = TextBox121.Value 
            .Offset(2, 18) = TextBox122.Value 
            .Offset(2, 19) = TextBox123.Value 
            .Offset(2, 21) = TextBox124.Value 
            .Offset(2, 22) = TextBox125.Value 
            .Offset(2, 23) = TextBox126.Value 
            .Offset(2, 24) = TextBox127.Value 
            .Offset(2, 26) = TextBox128.Value 
            .Offset(2, 27) = TextBox129.Value 
            .Offset(2, 28) = TextBox130.Value 
            .Offset(2, 29) = TextBox131.Value 
            .Offset(2, 31) = TextBox132.Value 
            .Offset(2, 32) = TextBox133.Value 
            .Offset(2, 33) = TextBox134.Value 
            .Offset(2, 34) = TextBox135.Value 
             
        End With 
         
         'If optNewData = True Then
         '    .Offset(0, 101).Value = "New"
         'ElseIf optCorrection = True Then
         '    .Offset(0, 101).Value = "Corrected"
         'End If
         
        Range("A1").Select 
    End Sub 
     
     
    Private Sub UserForm_Initialize() 
         
        With Range("Data") 
            Set rgData = .Rows(2) 
            Call LoadRecord 
        End With 
        MultiPage1.Value = 0 
         
        txtDate.Value = Now() 
        txtDate = Format(txtDate, "mm/dd/yyyy") 
        txtTime.Value = Now() 
        txtTime = Format(txtTime, "hh:mm am/pm") 
        txtTonsDel.Value = "" 
        txtRunTime.Value = "" 
        cmbPlant.Value = "" 
        txt5Beg.Value = "" 
        txt5End.Value = "" 
        txtACTons.Value = "" 
        txt2Beg.Value = "" 
        txt2End.Value = "" 
        cmbMixA.Value = "" 
        txtMixA1.Value = 0 
        txtMixA2.Value = 0 
        txtMixA3.Value = 0 
        txtMixA4.Value = 0 
        txtMixA5.Value = 0 
        txtMixA6.Value = 0 
        txtMixA7.Value = 0 
        txtMixAQ.Value = 0 
        cmbMixB.Value = "" 
        txtMixB1.Value = 0 
        txtMixB2.Value = 0 
        txtMixB3.Value = 0 
        txtMixB4.Value = 0 
        txtMixB5.Value = 0 
        txtMixB6.Value = 0 
        txtMixB7.Value = 0 
        txtMixBQ.Value = 0 
        cmbMixC.Value = "" 
        txtMixC1.Value = 0 
        txtMixC2.Value = 0 
        txtMixC3.Value = 0 
        txtMixC4.Value = 0 
        txtMixC5.Value = 0 
        txtMixC6.Value = 0 
        txtMixC7.Value = 0 
        txtMixCQ.Value = 0 
        cmbWeatherAM.Value = "" 
        cmbWeatherPM.Value = "" 
        cmbWaste1.Value = "" 
        txtWaste1A.Value = "" 
        txtWaste1B.Value = "" 
        cmbWaste2.Value = "" 
        txtWaste2A.Value = "" 
        txtWaste2B.Value = "" 
        cmbWaste3.Value = "" 
        txtWaste3A.Value = "" 
        txtWaste3B.Value = "" 
        cmbWaste4.Value = "" 
        txtWaste4A.Value = "" 
        txtWaste4B.Value = "" 
        cmbEmp1.Value = "" 
        txtCrew1.Value = "" 
        txtHour1.Value = "" 
        cmbEmp2.Value = "" 
        txtCrew2.Value = "" 
        txtHour2.Value = "" 
        cmbEmp3.Value = "" 
        txtCrew3.Value = "" 
        txtHour3.Value = "" 
        cmbEmp4.Value = "" 
        txtCrew4.Value = "" 
        txtHour4.Value = "" 
        cmbEmp5.Value = "" 
        txtCrew5.Value = "" 
        txtHour5.Value = "" 
        txtSup1.Value = "" 
        txtLATons1.Value = "" 
        txtHauler1.Value = "" 
        txtOrdered1.Value = "" 
        txtDeliv1.Value = "" 
        txtSup2.Value = "" 
        txtLATons2.Value = "" 
        txtHauler2.Value = "" 
        txtOrdered2.Value = "" 
        txtDeliv2.Value = "" 
        txtSup3.Value = "" 
        txtLATons3.Value = "" 
        txtHauler3.Value = "" 
        txtOrdered3.Value = "" 
        txtDeliv3.Value = "" 
        txtNotes.Value = "" 
         
         
         
        cmbEmp1.RowSource = "crew" 
        cmbEmp2.RowSource = "crew" 
        cmbEmp3.RowSource = "crew" 
        cmbEmp4.RowSource = "crew" 
        cmbEmp5.RowSource = "crew" 
        cmbWeatherAM.RowSource = "Weather" 
        cmbWeatherPM.RowSource = "Weather" 
        cmbWeatherAM.Value = "" 
        cmbWeatherPM.Value = "" 
        cmbPlant.RowSource = "Plants" 
        cmbPlant.Value = "" 
        cmbPlant.SetFocus 
        optNewData = True 
    End Sub 
     ' Filter Mix Designs by Plant
    Private Sub cmbPlant_Change() 
        If cmbPlant.Value = "" Then cmbMixA.RowSource = "MixDes0" 
        If cmbPlant.Value = 4 Then cmbMixA.RowSource = "MixDes4" 
        If cmbPlant.Value = 6 Then cmbMixA.RowSource = "MixDes6" 
        If cmbPlant.Value = 7 Then cmbMixA.RowSource = "MixDes7" 
        If cmbPlant.Value = "" Then cmbMixB.RowSource = "MixDes0" 
        If cmbPlant.Value = 4 Then cmbMixB.RowSource = "MixDes4" 
        If cmbPlant.Value = 6 Then cmbMixB.RowSource = "MixDes6" 
        If cmbPlant.Value = 7 Then cmbMixB.RowSource = "MixDes7" 
        If cmbPlant.Value = "" Then cmbMixC.RowSource = "MixDes0" 
        If cmbPlant.Value = 4 Then cmbMixC.RowSource = "MixDes4" 
        If cmbPlant.Value = 6 Then cmbMixC.RowSource = "MixDes6" 
        If cmbPlant.Value = 7 Then cmbMixC.RowSource = "MixDes7" 
    End Sub 
     
    Private Sub cmbPlant_Exit(ByVal Cancel As MSForms.ReturnBoolean) 
         
        If m_blnCancel Then Exit Sub 
         
        If cmbPlant.MatchFound = False Then 
            cmbPlant.BackColor = &HC0& 
            If MsgBox("Required!" & vbNewLine & "Please Select Correct Plant Number", vbOKOnly + vbExclamation, "Plant Number") = vbCancel Then Exit Sub 
            Cancel = True 
        Else 
            cmbPlant.BackColor = &H80000005 
        End If 
    End Sub 
     'Private Sub cmbPlant_Exit(ByVal Cancel As MSForms.ReturnBoolean)
     '        If cmbPlant.MatchFound = False Then
     '        cmbPlant.BackColor = &HC0&
     '        If MsgBox("Required!" & vbNewLine & "Please Select Correct Plant Number", vbOKOnly + vbExclamation, "Plant Number") = vbCancel Then Exit Sub
     '        Cancel = True
     '    Else
     '        cmbPlant.BackColor = &H80000005
     '    End If
     'End Sub
     ' Set offset Plant Crew
     
    Private Sub cmbEmp1_Change() 
        If cmbEmp1.Value = "" Then Exit Sub 
        txtCrew1.Text = cmbEmp1.List(cmbEmp1.ListIndex, 1) 
    End Sub 
     ' Set offset Plant Crew
    Private Sub cmbEmp2_Change() 
        If cmbEmp2.Value = "" Then Exit Sub 
        txtCrew2.Text = cmbEmp2.List(cmbEmp2.ListIndex, 1) 
    End Sub 
     ' Set offset Plant Crew
    Private Sub cmbEmp3_Change() 
        If cmbEmp3.Value = "" Then Exit Sub 
        txtCrew3.Text = cmbEmp3.List(cmbEmp3.ListIndex, 1) 
    End Sub 
     ' Set offset Plant Crew
    Private Sub cmbEmp4_Change() 
        If cmbEmp4.Value = "" Then Exit Sub 
        txtCrew4.Text = cmbEmp4.List(cmbEmp4.ListIndex, 1) 
    End Sub 
     ' Set offset Plant Crew
    Private Sub cmbEmp5_Change() 
        If cmbEmp5.Value = "" Then Exit Sub 
        txtCrew5.Text = cmbEmp5.List(cmbEmp5.ListIndex, 1) 
    End Sub 
     
     ' Set offset for Mix Design Components
    Private Sub cmbMixA_Change() 
        If cmbMixA.Value = "" Then Exit Sub 
        txtMixA1.Text = cmbMixA.List(cmbMixA.ListIndex, 10) 
        txtMixA2.Text = cmbMixA.List(cmbMixA.ListIndex, 15) 
        txtMixA3.Text = cmbMixA.List(cmbMixA.ListIndex, 20) 
        txtMixA4.Text = cmbMixA.List(cmbMixA.ListIndex, 25) 
        txtMixA5.Text = cmbMixA.List(cmbMixA.ListIndex, 30) 
        txtMixA6.Text = cmbMixA.List(cmbMixA.ListIndex, 35) 
        txtMixA7.Text = cmbMixA.List(cmbMixA.ListIndex, 5) 
         
         'Additional Data From Mix Design Sheet For Mix A
         
        TextBox55.Text = cmbMixA.List(cmbMixA.ListIndex, 1) 
        TextBox56.Text = cmbMixA.List(cmbMixA.ListIndex, 3) 
        TextBox57.Text = cmbMixA.List(cmbMixA.ListIndex, 4) 
        TextBox58.Text = cmbMixA.List(cmbMixA.ListIndex, 6) 
        TextBox59.Text = cmbMixA.List(cmbMixA.ListIndex, 7) 
        TextBox60.Text = cmbMixA.List(cmbMixA.ListIndex, 8) 
        TextBox61.Text = cmbMixA.List(cmbMixA.ListIndex, 9) 
        TextBox62.Text = cmbMixA.List(cmbMixA.ListIndex, 11) 
        TextBox63.Text = cmbMixA.List(cmbMixA.ListIndex, 12) 
        TextBox64.Text = cmbMixA.List(cmbMixA.ListIndex, 13) 
        TextBox65.Text = cmbMixA.List(cmbMixA.ListIndex, 14) 
        TextBox66.Text = cmbMixA.List(cmbMixA.ListIndex, 16) 
        TextBox67.Text = cmbMixA.List(cmbMixA.ListIndex, 17) 
        TextBox68.Text = cmbMixA.List(cmbMixA.ListIndex, 18) 
        TextBox69.Text = cmbMixA.List(cmbMixA.ListIndex, 19) 
        TextBox70.Text = cmbMixA.List(cmbMixA.ListIndex, 21) 
        TextBox71.Text = cmbMixA.List(cmbMixA.ListIndex, 22) 
        TextBox72.Text = cmbMixA.List(cmbMixA.ListIndex, 23) 
        TextBox73.Text = cmbMixA.List(cmbMixA.ListIndex, 24) 
        TextBox74.Text = cmbMixA.List(cmbMixA.ListIndex, 26) 
        TextBox75.Text = cmbMixA.List(cmbMixA.ListIndex, 27) 
        TextBox76.Text = cmbMixA.List(cmbMixA.ListIndex, 28) 
        TextBox77.Text = cmbMixA.List(cmbMixA.ListIndex, 29) 
        TextBox78.Text = cmbMixA.List(cmbMixA.ListIndex, 31) 
        TextBox79.Text = cmbMixA.List(cmbMixA.ListIndex, 32) 
        TextBox80.Text = cmbMixA.List(cmbMixA.ListIndex, 33) 
        TextBox81.Text = cmbMixA.List(cmbMixA.ListIndex, 34) 
    End Sub 
     
     ' Set offset for Mix Design Components
    Private Sub cmbMixB_Change() 
        If cmbMixB.Value = "" Then Exit Sub 
        txtMixB1.Text = cmbMixB.List(cmbMixB.ListIndex, 10) 
        txtMixB2.Text = cmbMixB.List(cmbMixB.ListIndex, 15) 
        txtMixB3.Text = cmbMixB.List(cmbMixB.ListIndex, 20) 
        txtMixB4.Text = cmbMixB.List(cmbMixB.ListIndex, 25) 
        txtMixB5.Text = cmbMixB.List(cmbMixB.ListIndex, 30) 
        txtMixB6.Text = cmbMixB.List(cmbMixB.ListIndex, 35) 
        txtMixB7.Text = cmbMixB.List(cmbMixB.ListIndex, 5) 
         
         'Additional Data From Mix Design Sheet For Mix B
         
        TextBox82.Text = cmbMixB.List(cmbMixB.ListIndex, 1) 
        TextBox83.Text = cmbMixB.List(cmbMixB.ListIndex, 3) 
        TextBox84.Text = cmbMixB.List(cmbMixB.ListIndex, 4) 
        TextBox85.Text = cmbMixB.List(cmbMixB.ListIndex, 6) 
        TextBox86.Text = cmbMixB.List(cmbMixB.ListIndex, 7) 
        TextBox87.Text = cmbMixB.List(cmbMixB.ListIndex, 8) 
        TextBox88.Text = cmbMixB.List(cmbMixB.ListIndex, 9) 
        TextBox89.Text = cmbMixB.List(cmbMixB.ListIndex, 11) 
        TextBox90.Text = cmbMixB.List(cmbMixB.ListIndex, 12) 
        TextBox91.Text = cmbMixB.List(cmbMixB.ListIndex, 13) 
        TextBox92.Text = cmbMixB.List(cmbMixB.ListIndex, 14) 
        TextBox93.Text = cmbMixB.List(cmbMixB.ListIndex, 16) 
        TextBox94.Text = cmbMixB.List(cmbMixB.ListIndex, 17) 
        TextBox95.Text = cmbMixB.List(cmbMixB.ListIndex, 18) 
        TextBox96.Text = cmbMixB.List(cmbMixB.ListIndex, 19) 
        TextBox97.Text = cmbMixB.List(cmbMixB.ListIndex, 21) 
        TextBox98.Text = cmbMixB.List(cmbMixB.ListIndex, 22) 
        TextBox99.Text = cmbMixB.List(cmbMixB.ListIndex, 23) 
        TextBox100.Text = cmbMixB.List(cmbMixB.ListIndex, 24) 
        TextBox101.Text = cmbMixB.List(cmbMixB.ListIndex, 26) 
        TextBox102.Text = cmbMixB.List(cmbMixB.ListIndex, 27) 
        TextBox103.Text = cmbMixB.List(cmbMixB.ListIndex, 28) 
        TextBox104.Text = cmbMixB.List(cmbMixB.ListIndex, 29) 
        TextBox105.Text = cmbMixB.List(cmbMixB.ListIndex, 31) 
        TextBox106.Text = cmbMixB.List(cmbMixB.ListIndex, 32) 
        TextBox107.Text = cmbMixB.List(cmbMixB.ListIndex, 33) 
        TextBox108.Text = cmbMixB.List(cmbMixB.ListIndex, 34) 
         
    End Sub 
     ' Set offset for Mix Design Components
    Private Sub cmbMixC_Change() 
        If cmbMixC.Value = "" Then Exit Sub 
        txtMixC1.Text = cmbMixC.List(cmbMixC.ListIndex, 10) 
        txtMixC2.Text = cmbMixC.List(cmbMixC.ListIndex, 15) 
        txtMixC3.Text = cmbMixC.List(cmbMixC.ListIndex, 20) 
        txtMixC4.Text = cmbMixC.List(cmbMixC.ListIndex, 25) 
        txtMixC5.Text = cmbMixC.List(cmbMixC.ListIndex, 30) 
        txtMixC6.Text = cmbMixC.List(cmbMixC.ListIndex, 35) 
        txtMixC7.Text = cmbMixC.List(cmbMixC.ListIndex, 5) 
         
         'Additional Data From Mix Design Sheet For Mix C
         
        TextBox109.Text = cmbMixC.List(cmbMixC.ListIndex, 1) 
        TextBox110.Text = cmbMixC.List(cmbMixC.ListIndex, 3) 
        TextBox111.Text = cmbMixC.List(cmbMixC.ListIndex, 4) 
        TextBox112.Text = cmbMixC.List(cmbMixC.ListIndex, 6) 
        TextBox113.Text = cmbMixC.List(cmbMixC.ListIndex, 7) 
        TextBox114.Text = cmbMixC.List(cmbMixC.ListIndex, 8) 
        TextBox115.Text = cmbMixC.List(cmbMixC.ListIndex, 9) 
        TextBox116.Text = cmbMixC.List(cmbMixC.ListIndex, 11) 
        TextBox117.Text = cmbMixC.List(cmbMixC.ListIndex, 12) 
        TextBox118.Text = cmbMixC.List(cmbMixC.ListIndex, 13) 
        TextBox119.Text = cmbMixC.List(cmbMixC.ListIndex, 14) 
        TextBox120.Text = cmbMixC.List(cmbMixC.ListIndex, 16) 
        TextBox121.Text = cmbMixC.List(cmbMixC.ListIndex, 17) 
        TextBox122.Text = cmbMixC.List(cmbMixC.ListIndex, 18) 
        TextBox123.Text = cmbMixC.List(cmbMixC.ListIndex, 19) 
        TextBox124.Text = cmbMixC.List(cmbMixC.ListIndex, 21) 
        TextBox125.Text = cmbMixC.List(cmbMixC.ListIndex, 22) 
        TextBox126.Text = cmbMixC.List(cmbMixC.ListIndex, 23) 
        TextBox127.Text = cmbMixC.List(cmbMixC.ListIndex, 24) 
        TextBox128.Text = cmbMixC.List(cmbMixC.ListIndex, 26) 
        TextBox129.Text = cmbMixC.List(cmbMixC.ListIndex, 27) 
        TextBox130.Text = cmbMixC.List(cmbMixC.ListIndex, 28) 
        TextBox131.Text = cmbMixC.List(cmbMixC.ListIndex, 29) 
        TextBox132.Text = cmbMixC.List(cmbMixC.ListIndex, 31) 
        TextBox133.Text = cmbMixC.List(cmbMixC.ListIndex, 32) 
        TextBox134.Text = cmbMixC.List(cmbMixC.ListIndex, 33) 
        TextBox135.Text = cmbMixC.List(cmbMixC.ListIndex, 34) 
         
    End Sub 
     
    Private Sub Calculate() 
         'Copy values from UserForm1 controls to Data array
        vaData(1, 1) = txtMixA1.Value 
        vaData(1, 2) = txtMixA2.Value 
        vaData(1, 3) = txtMixA3.Value 
        vaData(1, 4) = txtMixA4.Value 
        vaData(1, 5) = txtMixA5.Value 
        vaData(1, 6) = txtMixA6.Value 
        vaData(1, 8) = txtMixB1.Value 
        vaData(1, 9) = txtMixB2.Value 
        vaData(1, 10) = txtMixB3.Value 
        vaData(1, 11) = txtMixB4.Value 
        vaData(1, 12) = txtMixB5.Value 
        vaData(1, 13) = txtMixB6.Value 
        vaData(1, 15) = txtMixC1.Value 
        vaData(1, 16) = txtMixC2.Value 
        vaData(1, 17) = txtMixC3.Value 
        vaData(1, 18) = txtMixC4.Value 
        vaData(1, 19) = txtMixC5.Value 
        vaData(1, 20) = txtMixC6.Value 
        lblMixA.Caption = Val(txtMixA1.Value) + Val(txtMixA2.Value) + Val(txtMixA3.Value) + Val(txtMixA4.Value) + Val(txtMixA5.Value) + Val(txtMixA6.Value) 
        lblMixB.Caption = Val(txtMixB1.Value) + Val(txtMixB2.Value) + Val(txtMixB3.Value) + Val(txtMixB4.Value) + Val(txtMixB5.Value) + Val(txtMixB6.Value) 
        lblMixC.Caption = Val(txtMixC1.Value) + Val(txtMixC2.Value) + Val(txtMixC3.Value) + Val(txtMixC4.Value) + Val(txtMixC5.Value) + Val(txtMixC6.Value) 
        vaData(1, 7) = lblMixA.Caption 
        vaData(1, 14) = lblMixB.Caption 
        vaData(1, 21) = lblMixC.Caption 
         'Assign Data array values to current record in Database
        rgData.Value = vaData 
    End Sub 
    Private Sub txtMixA1_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixA2_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixA3_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixA4_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixA5_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixA6_Change() 
        Call Calculate 
    End Sub 
     
    Private Sub txtMixb1_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixb2_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixb3_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixb4_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixb5_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixb6_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixc1_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixc2_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixc3_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixc4_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixc5_Change() 
        Call Calculate 
    End Sub 
    Private Sub txtMixc6_Change() 
        Call Calculate 
    End Sub 
     
     ' AC - change string to value to do Percentage
    Private Sub txtTonsDel_Change() 
        If txtTonsDel.Value = "" Then Exit Sub 
        If txtACTons.Value = "" Then Exit Sub 
        lblACPer.Caption = FormatPercent((CDbl(txtACTons.Value) / CDbl(txtTonsDel.Value)), 1) 
    End Sub 
     'AC - change string to value to do Percentage
    Private Sub txtACTons_Change() 
        If txtACTons.Value = "" Then Exit Sub 
        If txtTonsDel.Value = "" Then Exit Sub 
        lblACPer.Caption = FormatPercent((CDbl(txtACTons.Value) / CDbl(txtTonsDel.Value)), 1) 
    End Sub 
     ' Tons Produced change string to value to do Addition
    Private Sub txtMixAQ_Change() 
        If txtMixAQ.Value = "" Then Exit Sub 
        If txtMixBQ.Value = "" Then Exit Sub 
        If txtMixCQ.Value = "" Then Exit Sub 
        lblPlantProd.Caption = CDbl(txtMixAQ.Value) + CDbl(txtMixBQ.Value) + CDbl(txtMixCQ.Value) 
    End Sub 
     ' Tons Produced change string to value to do Addition
    Private Sub txtMixBQ_Change() 
        If txtMixAQ.Value = "" Then Exit Sub 
        If txtMixBQ.Value = "" Then Exit Sub 
        If txtMixCQ.Value = "" Then Exit Sub 
        lblPlantProd.Caption = CDbl(txtMixAQ.Value) + CDbl(txtMixBQ.Value) + CDbl(txtMixCQ.Value) 
    End Sub 
     ' Tons Produced change string to value to do Addition
    Private Sub txtMixCQ_Change() 
        If txtMixAQ.Value = "" Then Exit Sub 
        If txtMixBQ.Value = "" Then Exit Sub 
        If txtMixCQ.Value = "" Then Exit Sub 
        lblPlantProd.Caption = CDbl(txtMixAQ.Value) + CDbl(txtMixBQ.Value) + CDbl(txtMixCQ.Value) 
    End Sub 
     ' # 5 Fuel - change string to value to do subtraction
    Private Sub txt5beg_Change() 
        If txt5Beg.Value = "" Then Exit Sub 
        If txt5End.Value = "" Then Exit Sub 
        lbl5Tot.Caption = CDbl(txt5Beg.Value) - CDbl(txt5End.Value) 
    End Sub 
     ' # 5 Fuel - change string to value to do subtraction
    Private Sub txt5End_Change() 
        If txt5Beg.Value = "" Then Exit Sub 
        If txt5End.Value = "" Then Exit Sub 
        lbl5Tot.Caption = CDbl(txt5Beg.Value) - CDbl(txt5End.Value) 
    End Sub 
     ' # 2 Fuel - change string to value to do subtraction
    Private Sub txt2beg_Change() 
        If txt2Beg.Value = "" Then Exit Sub 
        If txt2End.Value = "" Then Exit Sub 
        lbl2Tot.Caption = CDbl(txt2Beg.Value) - CDbl(txt2End.Value) 
    End Sub 
     ' # 5 Fuel - change string to value to do subtraction
    Private Sub txt2End_Change() 
        If txt2Beg.Value = "" Then Exit Sub 
        If txt2End.Value = "" Then Exit Sub 
        lbl2Tot.Caption = CDbl(txt2Beg.Value) - CDbl(txt2End.Value) 
         
    End Sub 
     
    Private Sub UserForm_QueryClose _ 
        (Cancel As Integer, CloseMode As Integer) 
        If CloseMode = vbFormControlMenu Then 
            MsgBox "You Must Click The 'Enter Data'" & vbNewLine & "or 'Cancel' Button To Close This Form." 
            Cancel = True 
        End If 
    End Sub 
     
    Private Sub LoadRecord() 
         'Copy values in rgData from worksheet to vaData array
        vaData = rgData.Value 
        Call Calculate 
    End Sub 
     
    Sub findLastDate() 
         
        Dim LastDate As Range 
        Set LastDate = Worksheets("Plant Production").Range("A1").End(xlDown) 
        Worksheets("Entry").Range("E17").Value = LastDate.Value 
    End Sub 
    
    

    I think I might be close to being done with this one though :)

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2004
    Posts
    10,541

    Re: SetFocus on Multi Page UserForm

    Steve

    If that code is doing what you want then there's nothing wrong with it.

    But at first look I do see some repetition/redundacy.

    I'll take a closer look and perhaps post back to give you some pointers on what I mean.

    If your almost done with this particular project you can always keep them in mind for future ones.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th May 2006
    Posts
    77

    Re: SetFocus on Multi Page UserForm

    Yeah, Thats why I went ahead and posted it,

    If I can get some constructive criticism and advice from an experienced coder, it will help alot on the next project.

    thanks again
    Last edited by Steve M; June 19th, 2006 at 02:30.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    14th July 2004
    Posts
    10,541

    Re: SetFocus on Multi Page UserForm

    Steve

    Here's a small start.

    This.
    VB:
    Private Sub cmbPlant_Change() 
        If cmbPlant.Value = "" Then cmbMixA.RowSource = "MixDes0" 
        If cmbPlant.Value = 4 Then cmbMixA.RowSource = "MixDes4" 
        If cmbPlant.Value = 6 Then cmbMixA.RowSource = "MixDes6" 
        If cmbPlant.Value = 7 Then cmbMixA.RowSource = "MixDes7" 
        If cmbPlant.Value = "" Then cmbMixB.RowSource = "MixDes0" 
        If cmbPlant.Value = 4 Then cmbMixB.RowSource = "MixDes4" 
        If cmbPlant.Value = 6 Then cmbMixB.RowSource = "MixDes6" 
        If cmbPlant.Value = 7 Then cmbMixB.RowSource = "MixDes7" 
        If cmbPlant.Value = "" Then cmbMixC.RowSource = "MixDes0" 
        If cmbPlant.Value = 4 Then cmbMixC.RowSource = "MixDes4" 
        If cmbPlant.Value = 6 Then cmbMixC.RowSource = "MixDes6" 
        If cmbPlant.Value = 7 Then cmbMixC.RowSource = "MixDes7" 
    End Sub 
    
    
    Can be replaced with this.
    VB:
    Private Sub cmbPlant_Change() 
        Dim I As Long 
        Dim var As Long 
        Select Case cmbPlant.Value 
        Case "" 
            var = 0 
        Case 4, 6, 7 
            var = cmbPlant.Value 
        End Select 
         
        For I = Asc("A") To Asc("C") 
            Me.Controls("cmbMix" & Chr(I)) = "MixDes" & var 
        Next I 
    End Sub 
    
    
    I know the code isn't shorter but hopefully it illustrates how you can refer to controls on the userform.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    26th May 2006
    Posts
    77

    Re: SetFocus on Multi Page UserForm

    Thanks, I'll try it..

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. UserForm Multi-Page Wizard
    By terracotta in forum EXCEL HELP
    Replies: 4
    Last Post: March 18th, 2008, 19:01
  2. Set Up Multi Page To Mimic Userform Initialize
    By nu@this in forum EXCEL HELP
    Replies: 4
    Last Post: August 2nd, 2007, 02:37
  3. Change Start Page Of Multi Page UserForm
    By teoant in forum EXCEL HELP
    Replies: 2
    Last Post: July 23rd, 2006, 04:42
  4. Switch pages on multi page userform
    By Doodlebug in forum EXCEL HELP
    Replies: 3
    Last Post: January 21st, 2006, 00:29
  5. Replies: 2
    Last Post: April 5th, 2003, 07:17

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno