Disable and Enable a Command Button on a Dynamically Changing Multipage Userform

Hi!

I have a multipage userform that has a save button. I would like to enable the save button on the last "active" page. Posted my codes below. Also, appreciate any help in trimming this down. Thanks so much!

Btw, newbie coder here.

Private Sub MultiPage1_change()

Select Case MultiPage1.value
'First Page:
Case 0
cmdprevmp.Enabled = False
cmdnextmp.Enabled = True
cmdsave.Enabled = False
'Last Page:
Case MultiPage1.Pages.Count - 1
cmdprevmp.Enabled = True
cmdnextmp.Enabled = False
cmdsave.Enabled = True
'Other Pages:
Case Else
cmdprevmp.Enabled = True
cmdnextmp.Enabled = True
cmdsave.Enabled = False
End Select
End Sub

Private Sub cmdClearmp_Click()
Dim ctl As Control

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.value = False
End If
Next ctl

End Sub
Private Sub cmdcancelmp_Click()
Unload Me
End Sub

Private Sub cmdnextmp_Click()
MultiPage1.value = MultiPage1.value + 1
End Sub

Private Sub cmdok_Click()
Dim pPage As Page, cCont As Control
Dim ctl As Control
If Not IsDate(Me.DTPickerpa.value) Then
MsgBox "The Date box must contain a date.", vbExclamation, "Employee Name"
Me.DTPickerpa.SetFocus
Exit Sub
End If

If Me.cmbempname.value = "" Then
MsgBox "Please choose employee name.", vbExclamation, "Employee Position"
Me.cmbempname.SetFocus
Exit Sub
End If

If Me.cmbemppos.value = "" Then
MsgBox "Please choose employee position.", vbExclamation, "Employee Schedule Start"
Me.cmbemppos.SetFocus
Exit Sub
End If

If Me.cmbempschedstart.value = "" Then
MsgBox "Please choose employee schedule start.", vbExclamation, "Employee Schedule End"
Me.cmbempschedstart.SetFocus
Exit Sub
End If

If Me.cmbempschedend.value = "" Then
MsgBox "Please choose employee sched end.", vbExclamation, "Employee Break"
Me.cmbempschedend.SetFocus
Exit Sub
End If

If Me.cmbempbreak.value = "" Then
MsgBox "Please choose employee break.", vbExclamation, "Station Assignment"
Me.cmbempbreak.SetFocus
Exit Sub
End If

If Not IsNumeric(Me.txtpop.value) Then
MsgBox "The % box must contain a number.", vbExclamation, "Staff Expenses"
Me.txtpop.SetFocus
Exit Sub
End If

If Not IsNumeric(Me.txtpw4cs.value) Then
MsgBox "The % box must contain a number.", vbExclamation, "Staff Expenses"
Me.txtpw4cs.SetFocus
Exit Sub
End If

If Not IsNumeric(Me.txtwarmer.value) Then
MsgBox "The % box must contain a number.", vbExclamation, "Staff Expenses"
Me.txtwarmer.SetFocus
Exit Sub
End If

If Not IsNumeric(Me.txtbev.value) Then
MsgBox "The % box must contain a number.", vbExclamation, "Staff Expenses"
Me.txtbev.SetFocus
Exit Sub
End If

If Not IsNumeric(Me.txtpos.value) Then
MsgBox "The % box must contain a number.", vbExclamation, "Staff Expenses"
Me.txtpos.SetFocus
Exit Sub
End If

If Not IsNumeric(Me.txtgriddle.value) Then
MsgBox "The % box must contain a number.", vbExclamation, "Staff Expenses"
Me.txtgriddle.SetFocus
Exit Sub
End If

If Not IsNumeric(Me.txtfryer.value) Then
MsgBox "The % box must contain a number.", vbExclamation, "Staff Expenses"
Me.txtfryer.SetFocus
Exit Sub
End If

If Not IsNumeric(Me.txtbacksink.value) Then
MsgBox "The % box must contain a number.", vbExclamation, "Staff Expenses"
Me.txtbacksink.SetFocus
Exit Sub
End If

Me.txttotal.value = Int(Me.txtpop.value) + Int(Me.txtpw4cs.value) + Int(Me.txtwarmer.value) _
+ Int(Me.txtbev.value) + Int(Me.txtpos.value) _
+ Int(Me.txtdispatch.value) + Int(Me.txtgriddle.value) + Int(Me.txtfryer.value) _
+ Int(Me.txtbacksink.value)

Call ok1
Call ok2
Call ok3

If Me.txttotal.value <> 100 Then
MsgBox "% Total must equal to 100."
Me.MultiPage1.Pages(1).Visible = False
Me.MultiPage1.Pages(2).Visible = False
Me.MultiPage1.Pages(3).Visible = False
Me.MultiPage1.Pages(4).Visible = False
Me.MultiPage1.Pages(5).Visible = False
Me.MultiPage1.Pages(6).Visible = False
Me.MultiPage1.Pages(7).Visible = False
Me.MultiPage1.Pages(8).Visible = False
Me.MultiPage1.Pages(9).Visible = False
Me.MultiPage1.Pages(10).Visible = False
Me.MultiPage1.Pages(11).Visible = False
Me.MultiPage1.Pages(12).Visible = False
End If

End Sub

Private Sub cmdprevmp_Click()
MultiPage1.value = MultiPage1.value - 1
End Sub

Private Sub cmdprevmp_Click()
MultiPage1.value = MultiPage1.value - 1
End Sub

Private Sub cmdsave_Click()
Dim ctl As Control

ActiveWorkbook.Sheets("database").Activate
Range("A1").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.value = Format(Now, "dd/mm/yyyy hh:nn:ss")
ActiveCell.Offset(0, 1) = Format(DTPickerpa.value, "dd/mm/yyyy")
ActiveCell.Offset(0, 2) = Me.cmbempname.value
ActiveCell.Offset(0, 3) = Me.cmbemppos.value
ActiveCell.Offset(0, 4) = Me.cmbempschedstart.value
ActiveCell.Offset(0, 5) = Me.cmbempschedend.value
ActiveCell.Offset(0, 6) = Me.cmbempbreak.value
ActiveCell.Offset(0, 7) = Me.txtpop.value
ActiveCell.Offset(0, 8) = Me.txtpw4cs.value
ActiveCell.Offset(0, 9) = Me.txtwarmer.value
ActiveCell.Offset(0, 10) = Me.txtbev.value
ActiveCell.Offset(0, 11) = Me.txtpos.value
ActiveCell.Offset(0, 12) = Me.txtdispatch.value
ActiveCell.Offset(0, 13) = Me.txtgriddle.value
ActiveCell.Offset(0, 14) = Me.txtfryer.value
ActiveCell.Offset(0, 15) = Me.txtbacksink.value

Call Savelstpop
Call savelstpw4cs
Call savelstwarmer
Call savelsttea
Call savelstpost
Call savelstpos
Call savelstorder
Call savelstgriddle
Call savelstfryer
Call savelstscale

Range("A1").Select

Call frmpa_initialize

MsgBox " Checklists saved. "

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.value = False
End If
Next ctl

End Sub

Public Sub Savelstpop()

If lstpop1.Selected(0) Then
ActiveCell.Offset(0, 16).value = "Yes"
Else
ActiveCell.Offset(0, 16).value = "No"
End If
If lstpop1.Selected(1) Then
ActiveCell.Offset(0, 17).value = "Yes"
Else
ActiveCell.Offset(0, 17).value = "No"
End If
If lstpop1.Selected(2) Then
ActiveCell.Offset(0, 18).value = "Yes"
Else
ActiveCell.Offset(0, 18).value = "No"
End If
If lstpop1.Selected(3) Then
ActiveCell.Offset(0, 19).value = "Yes"
Else
ActiveCell.Offset(0, 19).value = "No"
End If
If lstpop1.Selected(4) Then
ActiveCell.Offset(0, 20).value = "Yes"
Else
ActiveCell.Offset(0, 20).value = "No"
End If
If lstpop1.Selected(5) Then
ActiveCell.Offset(0, 21).value = "Yes"
Else
ActiveCell.Offset(0, 21).value = "No"
End If
If lstpop1.Selected(6) Then
ActiveCell.Offset(0, 22).value = "Yes"
Else
ActiveCell.Offset(0, 22).value = "No"
End If
If lstpop1.Selected(7) Then
ActiveCell.Offset(0, 23).value = "Yes"
Else
ActiveCell.Offset(0, 23).value = "No"
End If
If lstpop1.Selected(8) Then
ActiveCell.Offset(0, 24).value = "Yes"
Else
ActiveCell.Offset(0, 24).value = "No"
End If
If lstpop1.Selected(9) Then
ActiveCell.Offset(0, 25).value = "Yes"
Else
ActiveCell.Offset(0, 25).value = "No"
End If
If lstpop1.Selected(10) Then
ActiveCell.Offset(0, 26).value = "Yes"
Else
ActiveCell.Offset(0, 26).value = "No"
End If
If lstpop1.Selected(11) Then
ActiveCell.Offset(0, 27).value = "Yes"
Else
ActiveCell.Offset(0, 27).value = "No"
End If
If lstpop1.Selected(12) Then
ActiveCell.Offset(0, 28).value = "Yes"
Else
ActiveCell.Offset(0, 28).value = "No"
End If
If lstpop1.Selected(13) Then
ActiveCell.Offset(0, 29).value = "Yes"
Else
ActiveCell.Offset(0, 29).value = "No"
End If
If lstpop1.Selected(14) Then
ActiveCell.Offset(0, 30).value = "Yes"
Else
ActiveCell.Offset(0, 30).value = "No"
End If
If lstpop1.Selected(15) Then
ActiveCell.Offset(0, 31).value = "Yes"
Else
ActiveCell.Offset(0, 31).value = "No"
End If
If lstpop1.Selected(16) Then
ActiveCell.Offset(0, 32).value = "Yes"
Else
ActiveCell.Offset(0, 32).value = "No"
End If
If lstpop1.Selected(17) Then
ActiveCell.Offset(0, 33).value = "Yes"
Else
ActiveCell.Offset(0, 33).value = "No"
End If
If lstpop1.Selected(18) Then
ActiveCell.Offset(0, 34).value = "Yes"
Else
ActiveCell.Offset(0, 34).value = "No"
End If
If lstpop1.Selected(19) Then
ActiveCell.Offset(0, 35).value = "Yes"
Else
ActiveCell.Offset(0, 35).value = "No"
End If
If lstpop1.Selected(20) Then
ActiveCell.Offset(0, 36).value = "Yes"
Else
ActiveCell.Offset(0, 36).value = "No"
End If
If lstpop1.Selected(21) Then
ActiveCell.Offset(0, 37).value = "Yes"
Else
ActiveCell.Offset(0, 37).value = "No"
End If
If lstpop1.Selected(22) Then
ActiveCell.Offset(0, 38).value = "Yes"
Else
ActiveCell.Offset(0, 38).value = "No"
End If
If lstpop1.Selected(23) Then
ActiveCell.Offset(0, 39).value = "Yes"
Else
ActiveCell.Offset(0, 39).value = "No"
End If

End Sub

Public Sub frmpa_initialize()
Me.MultiPage1.Pages(0).Visible = True
Me.MultiPage1.Pages(1).Visible = False
Me.MultiPage1.Pages(2).Visible = False
Me.MultiPage1.Pages(3).Visible = False
Me.MultiPage1.Pages(4).Visible = False
Me.MultiPage1.Pages(5).Visible = False
Me.MultiPage1.Pages(6).Visible = False
Me.MultiPage1.Pages(7).Visible = False
Me.MultiPage1.Pages(8).Visible = False
Me.MultiPage1.Pages(9).Visible = False
Me.MultiPage1.Pages(10).Visible = False
Me.MultiPage1.Pages(11).Visible = False
Me.MultiPage1.Pages(12).Visible = False

End Sub

Public Sub ok1()

If txtpop.value > 0 And txtpw4cs.value > 0 And txtwarmer.value > 0 And _
txtbev.value > 0 And txtpos.value > 0 And txtdispatch.value > 0 And _
txtgriddle.value > 0 And txtfryer.value > 0 And txtbacksink.value > 0 Then

Me.MultiPage1.Pages(1).Visible = True
Me.MultiPage1.Pages(2).Visible = True
Me.MultiPage1.Pages(3).Visible = True
Me.MultiPage1.Pages(4).Visible = True
Me.MultiPage1.Pages(5).Visible = True
Me.MultiPage1.Pages(6).Visible = True
Me.MultiPage1.Pages(7).Visible = True
Me.MultiPage1.Pages(8).Visible = True
Me.MultiPage1.Pages(9).Visible = True
Me.MultiPage1.Pages(10).Visible = True
Me.MultiPage1.Pages(11).Visible = True
Me.MultiPage1.Pages(12).Visible = True

ElseIf txtpop.value = 0 And txtpw4cs.value = 0 And txtwarmer.value = 0 And _
txtbev.value = 0 And txtpos.value = 0 And txtdispatch.value = 0 And _
txtgriddle.value = 0 And txtfryer.value = 0 And txtbacksink.value = 0 Then

Me.MultiPage1.Pages(1).Visible = False
Me.MultiPage1.Pages(2).Visible = False
Me.MultiPage1.Pages(3).Visible = False
Me.MultiPage1.Pages(4).Visible = False
Me.MultiPage1.Pages(5).Visible = False
Me.MultiPage1.Pages(6).Visible = False
Me.MultiPage1.Pages(7).Visible = False
Me.MultiPage1.Pages(8).Visible = False
Me.MultiPage1.Pages(9).Visible = False
Me.MultiPage1.Pages(10).Visible = False
Me.MultiPage1.Pages(11).Visible = False
Me.MultiPage1.Pages(12).Visible = False

ElseIf txtpop.value = 0 And txtpw4cs.value > 0 And txtwarmer.value > 0 And _
txtbev.value > 0 And txtpos.value > 0 And txtdispatch.value > 0 And _
txtgriddle.value > 0 And txtfryer.value > 0 And txtbacksink.value > 0 Then

Me.MultiPage1.Pages(1).Visible = False
Me.MultiPage1.Pages(2).Visible = True
Me.MultiPage1.Pages(3).Visible = True
Me.MultiPage1.Pages(4).Visible = True
Me.MultiPage1.Pages(5).Visible = True
Me.MultiPage1.Pages(6).Visible = True
Me.MultiPage1.Pages(7).Visible = True
Me.MultiPage1.Pages(8).Visible = True
Me.MultiPage1.Pages(9).Visible = True
Me.MultiPage1.Pages(10).Visible = True
Me.MultiPage1.Pages(11).Visible = True
Me.MultiPage1.Pages(12).Visible = True

ElseIf txtpop.value = 0 And txtpw4cs.value = 0 And txtwarmer.value > 0 And _
txtbev.value > 0 And txtpos.value > 0 And txtdispatch.value > 0 And _
txtgriddle.value > 0 And txtfryer.value > 0 And txtbacksink.value > 0 Then

Me.MultiPage1.Pages(1).Visible = False
Me.MultiPage1.Pages(2).Visible = False
Me.MultiPage1.Pages(3).Visible = True
Me.MultiPage1.Pages(4).Visible = True
Me.MultiPage1.Pages(5).Visible = True
Me.MultiPage1.Pages(6).Visible = True
Me.MultiPage1.Pages(7).Visible = True
Me.MultiPage1.Pages(8).Visible = True
Me.MultiPage1.Pages(9).Visible = True
Me.MultiPage1.Pages(10).Visible = True
Me.MultiPage1.Pages(11).Visible = True
Me.MultiPage1.Pages(12).Visible = True

End If

End Sub

Save Button CmdSave
On the Initialize set the Me.CmdSave.Visible=False
When you have the MultiPage1.Pages(12).Visible=True
Set the CmdSave.Visible=True

Originally Posted by CRIMEDOG

Save Button CmdSave
On the Initialize set the Me.CmdSave.Visible=False
When you have the MultiPage1.Pages(12).Visible=True
Set the CmdSave.Visible=True

thanks crimedog. my multipage changes, and MultiPage1.Pages(12) is dynamic and isn't always visible. is it possible to count visible pages only. something like MultiPage1.visible.Count - 1?