Announcement

Collapse
No announcement yet.

SetFocus on Multi Page UserForm

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.
    Boo!

    Comment


    • #3
      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
      Code:
      MultiPage1.Value = 0
      to my initialize event and it seems to be working OK.

      Thanks for your help though.

      Comment


      • #4
        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...
        Boo!

        Comment


        • #5
          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...

          Code:
          '==============================================================
          '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

          Comment


          • #6
            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.
            Boo!

            Comment


            • #7
              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, 01:30.

              Comment


              • #8
                Re: SetFocus on Multi Page UserForm

                Steve

                Here's a small start.

                This.
                Code:
                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.
                Code:
                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.
                Boo!

                Comment


                • #9


                  Re: SetFocus on Multi Page UserForm

                  Thanks, I'll try it..

                  Comment

                  Working...
                  X