Vonsteiner
New Member
- Joined
- Apr 14, 2014
- Messages
- 45
- Office Version
- 365
- Platform
- Windows
Hello,
I am creating a workbook where the user inputs data with 17 userforms. The first four userforms always show up. On the first userform I have 13 check boxes which determine if any of the other userforms are activated. If the checkbox is selected the associated userform will become active. If the checkbox is not selected then the userform will not become active. Once the user completes all the active forms the workbook creates an index worksheet. The index worksheet has 17 buttons for each userform and will be used to update the data on whichever userform button is selected. All of this works when initially inputting data into the userform. The problem comes in when I select to update the first userform. All the data from the worksheet is there and any changes I make will input the data correctly into the worksheet. However, if I select a checkbox in the update form the userform associated with the checkbox does not become active and the worksheet doesn't become visible. The value of the checkbox changes from False to True, but the rest of the sub doesn't work. The PDUpdate form is the one that is not working. I have other issues I am working through but this is the main one at the moment.
ufProjDesc code-this is the first userform where the checkboxes work:
This is the ufPDUpdate code where the checkboxes return the correct value but do clicking the update button doesn't seem to run the HideSheets or ClearCells routines.
I am creating a workbook where the user inputs data with 17 userforms. The first four userforms always show up. On the first userform I have 13 check boxes which determine if any of the other userforms are activated. If the checkbox is selected the associated userform will become active. If the checkbox is not selected then the userform will not become active. Once the user completes all the active forms the workbook creates an index worksheet. The index worksheet has 17 buttons for each userform and will be used to update the data on whichever userform button is selected. All of this works when initially inputting data into the userform. The problem comes in when I select to update the first userform. All the data from the worksheet is there and any changes I make will input the data correctly into the worksheet. However, if I select a checkbox in the update form the userform associated with the checkbox does not become active and the worksheet doesn't become visible. The value of the checkbox changes from False to True, but the rest of the sub doesn't work. The PDUpdate form is the one that is not working. I have other issues I am working through but this is the main one at the moment.
ufProjDesc code-this is the first userform where the checkboxes work:
Code:
Private Sub UserForm_Activate()
'Activate and unprotect worksheet
Worksheets("Project Description").Activate
'Set focus on first cell
txtAction.SetFocus
'Clear out the form
txtAction.Value = ""
txtClaimants.Value = ""
cboSales.Clear
cboInv.Clear
txtDF1.Value = ""
txtDF2.Value = ""
txtDF3.Value = ""
txtDF4.Value = ""
txtDF5.Value = ""
txtSetAmt.Value = ""
txtArchMT.Value = ""
txtProvMT.Value = ""
cboSA.Clear
txtSADate.Value = ""
chbLien.Value = False
chbStanQSF.Value = False
chbClaims.Value = False
chbAllocation.Value = False
chbRelease.Value = False
chbMDL.Value = False
chbPFS.Value = False
chbMedRec.Value = False
chbBenefits.Value = False
chbBankruptcy.Value = False
chbProbate.Value = False
chbEnhanced1.Value = False
chbEnhanced2.Value = False
txtNotes.Value = ""
'Fill combo boxes
With cboSales
.AddItem "Ag"
.AddItem "Al"
.AddItem "Bo"
.AddItem "Bn"
.AddItem "Gg"
.AddItem "Gn"
.AddItem "So"
.AddItem "Sn"
.AddItem "Wo"
End With
With cboInv
.AddItem "Global"
.AddItem "Inventory"
.AddItem "Inventory with Settlement Counsel"
End With
With cboSA
.AddItem "Yes"
.AddItem "No"
.AddItem "Unknown"
End With
ufProjDesc!txtAction.TabIndex = 0
ufProjDesc!txtClaimants.TabIndex = 1
ufProjDesc!cboSales.TabIndex = 2
ufProjDesc!cboInv.TabIndex = 3
ufProjDesc!txtDF1.TabIndex = 4
ufProjDesc!txtDF2.TabIndex = 5
ufProjDesc!txtDF3.TabIndex = 6
ufProjDesc!txtDF4.TabIndex = 7
ufProjDesc!txtDF5.TabIndex = 8
ufProjDesc!txtSetAmt.TabIndex = 9
ufProjDesc!txtArchMT.TabIndex = 10
ufProjDesc!txtProvMT.TabIndex = 11
ufProjDesc!cboSA.TabIndex = 12
ufProjDesc!txtSADate.TabIndex = 13
ufProjDesc!chbLien.TabIndex = 14
ufProjDesc!chbStanQSF.TabIndex = 15
ufProjDesc!chbClaims.TabIndex = 16
ufProjDesc!chbAllocation.TabIndex = 17
ufProjDesc!chbRelease.TabIndex = 18
ufProjDesc!chbMDL.TabIndex = 19
ufProjDesc!chbPFS.TabIndex = 20
ufProjDesc!chbMedRec.TabIndex = 21
ufProjDesc!chbBenefits.TabIndex = 22
ufProjDesc!chbBankruptcy.TabIndex = 23
ufProjDesc!chbProbate.TabIndex = 24
ufProjDesc!chbEnhanced1.TabIndex = 25
ufProjDesc!chbEnhanced2.TabIndex = 26
ufProjDesc!txtNotes.TabIndex = 27
ufProjDesc!cmdCancel.TabIndex = 28
ufProjDesc!cmdNext.TabIndex = 29
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdNext_Click()
Dim wb As Workbook
Dim wb2 As Workbook
Dim wbname As String
Dim wbname2 As String
Set wb = ActiveWorkbook
Set wb2 = ActiveWorkbook
Application.DisplayAlerts = False
'Make Project Description worksheet active
Worksheets("Project Description").Activate
'Gray out certain input boxes
Call CellLock
'Required cell input
If txtAction.Value = "" Then
MsgBox "You must include the Case/Action name", vbCritical
txtAction.SetFocus
Exit Sub
End If
If txtClaimants.Value = "" Then
MsgBox "You must include the number of claimants participating in the action", vbCritical
txtClaimants.SetFocus
Exit Sub
End If
If cboSales.Value = "" Then
MsgBox "You must include the salesperson for the action", vbCritical
cboSales.SetFocus
Exit Sub
End If
If cboInv.Value = "" Then
MsgBox "You must include the inventory category for this action", vbCritical
cboInv.SetFocus
Exit Sub
End If
If txtDF1.Value = "" Then
MsgBox "You must include the designated firm for this action", vbCritical
txtDF1.SetFocus
Exit Sub
End If
If txtArchMT.Value = "" Then
MsgBox "You must include the Archer mass tort allocation for this action", vbCritical
txtArchMT.SetFocus
Exit Sub
End If
If txtProvMT.Value = "" Then
MsgBox "You must include the Providio mass tort allocation for this action", vbCritical
txtProvMT.SetFocus
Exit Sub
End If
If cboSA.Value = "" Then
MsgBox "You must include whether the Services Agreement has been signed for this action", vbCritical
cboSA.SetFocus
Exit Sub
End If
If cboSA.Value = "Yes" And _
txtSADate.Value = "" Then
MsgBox "You must include the Service Agreement effective date", vbCritical
txtSADate.SetFocus
Exit Sub
End If
'Transfer information
Cells(1, 1).Value = txtAction.Value
Cells(2, 2).Value = txtClaimants.Value
Cells(2, 4).Value = cboSales.Value
Cells(3, 2).Value = cboInv.Value
Cells(4, 2).Value = txtDF1.Value
Cells(5, 2).Value = txtDF2.Value
Cells(6, 2).Value = txtDF3.Value
Cells(7, 2).Value = txtDF4.Value
Cells(8, 2).Value = txtDF5.Value
Cells(3, 4).Value = txtSetAmt.Value
Cells(9, 2).Value = txtArchMT.Value / 100
Cells(9, 4).Value = txtProvMT.Value / 100
Cells(10, 2).Value = cboSA.Value
Cells(10, 4).Value = txtSADate.Value
Cells(1, 7).Value = chbLien.Value
Cells(1, 9).Value = chbStanQSF.Value
Cells(1, 11).Value = chbClaims.Value
Cells(1, 13).Value = chbAllocation.Value
Cells(1, 15).Value = chbRelease.Value
Cells(1, 17).Value = chbMDL.Value
Cells(1, 19).Value = chbPFS.Value
Cells(1, 21).Value = chbMedRec.Value
Cells(1, 23).Value = chbBenefits.Value
Cells(1, 25).Value = chbBankruptcy.Value
Cells(1, 27).Value = chbProbate.Value
Cells(1, 29).Value = chbEnhanced1.Value
Cells(1, 31).Value = chbEnhanced2.Value
Cells(19, 2).Value = txtNotes.Value
'Clear content from unused services and hide the sheets
Call ClearCells
Call HideSheets
'Move to the next form
ufProjDesc.Hide
ufCaseInfo.Show
End Sub
Private Sub ClearCells()
Worksheets("Project Description").Activate
If Cells(1, 7).Value = False Then Cells(12, 2).ClearContents
If Cells(1, 9).Value = False Then Cells(13, 2).ClearContents
If Cells(1, 11).Value = False Then Cells(14, 2).ClearContents
If Cells(1, 13).Value = False Then Cells(15, 2).ClearContents
If Cells(1, 15).Value = False Then Cells(16, 2).ClearContents
If Cells(1, 17).Value = False Then Cells(17, 2).ClearContents
If Cells(1, 19).Value = False Then Cells(12, 3).ClearContents
If Cells(1, 21).Value = False Then Cells(13, 3).ClearContents
If Cells(1, 23).Value = False Then Cells(14, 3).ClearContents
If Cells(1, 25).Value = False Then Cells(15, 3).ClearContents
If Cells(1, 27).Value = False Then Cells(16, 3).ClearContents
If Cells(1, 29).Value = False Then Cells(17, 3).ClearContents
If Cells(1, 31).Value = False Then Cells(18, 3).ClearContents
End Sub
Private Sub HideSheets()
Worksheets("Project Description").Activate
If Cells(1, 7).Value = False Then Worksheets("Lien Resolution").Visible = False
If Cells(1, 9).Value = False Then Worksheets("Standard QSF Services").Visible = False
If Cells(1, 11).Value = False Then Worksheets("Claims Administration").Visible = False
If Cells(1, 13).Value = False Then Worksheets("Allocation Determinations").Visible = False
If Cells(1, 15).Value = False Then Worksheets("Release Administration").Visible = False
If Cells(1, 17).Value = False Then Worksheets("MDL PFS Portal (ASAP-Pre)").Visible = False
If Cells(1, 19).Value = False Then Worksheets("Plaintiff Fact Sheets").Visible = False
If Cells(1, 21).Value = False Then Worksheets("Medical Records Review").Visible = False
If Cells(1, 23).Value = False Then Worksheets("Benefits Analysis").Visible = False
If Cells(1, 25).Value = False Then Worksheets("Bankruptcy Coordination").Visible = False
If Cells(1, 27).Value = False Then Worksheets("Probate Coordination").Visible = False
If Cells(1, 29).Value = False Then Worksheets("Enhanced QSF (Firm directed)").Visible = False
If Cells(1, 31).Value = False Then Worksheets("Enhanced QSF (Full outsource)").Visible = False
End Sub
Private Sub CellLock()
If cboInv.Value = "Inventory" Or cboInv.Value = "Inventory with Settlement Counsel" Then
txtDesFirm2.Locked = True
txtDesFirm2.BackColor = &H80000011
txtDF2.Locked = True
txtDF2.BackColor = &H80000011
txtDesFirm3.Locked = True
txtDesFirm3.BackColor = &H80000011
txtDF3.Locked = True
txtDF3.BackColor = &H80000011
txtDesFirm4.Locked = True
txtDesFirm4.BackColor = &H80000011
txtDF4.Locked = True
txtDF4.BackColor = &H80000011
txtDesFirm5.Locked = True
txtDesFirm5.BackColor = &H80000011
txtDF5.Locked = True
txtDF5.BackColor = &H80000011
End If
End SubThis is the ufPDUpdate code where the checkboxes return the correct value but do clicking the update button doesn't seem to run the HideSheets or ClearCells routines.
Code:
Private Sub UserForm_Activate()
'Activate and unprotect worksheet
Worksheets("Project Description").Activate
'Set focus on first cell
txtAction.SetFocus
'Input data from the worksheet
txtAction.Value = Cells(1, 1).Value
txtClaimants.Value = Cells(2, 2).Value
cboSales.Value = Cells(2, 4).Value
cboInv.Value = Cells(3, 2).Value
txtDF1.Value = Cells(4, 2).Value
txtDF2.Value = Cells(5, 2).Value
txtDF3.Value = Cells(6, 2).Value
txtDF4.Value = Cells(7, 2).Value
txtDF5.Value = Cells(8, 2).Value
txtSetAmt.Value = Cells(3, 4).Value
txtArchMT.Value = Cells(9, 2).Value * 100
txtProvMT.Value = Cells(9, 4).Value * 100
cboSA.Value = Cells(10, 2).Value
txtSADate.Value = Cells(10, 4).Value
chbLien.Value = Cells(1, 7).Value
chbStanQSF.Value = Cells(1, 9).Value
chbClaims.Value = Cells(1, 11).Value
chbAllocation.Value = Cells(1, 13).Value
chbRelease.Value = Cells(1, 15).Value
chbMDL.Value = Cells(1, 17).Value
chbPFS.Value = Cells(1, 19).Value
chbMedRec.Value = Cells(1, 21).Value
chbBenefits.Value = Cells(1, 23).Value
chbBankruptcy.Value = Cells(1, 25).Value
chbProbate.Value = Cells(1, 27).Value
chbEnhanced1.Value = Cells(1, 29).Value
chbEnhanced2.Value = Cells(1, 31).Value
txtNotes.Value = Cells(19, 2).Value
'Fill combo boxes
With cboSales
.AddItem "Ag"
.AddItem "Al"
.AddItem "Bo"
.AddItem "Bn"
.AddItem "Gg"
.AddItem "Gn"
.AddItem "So"
.AddItem "Sn"
.AddItem "Wo"
End With
With cboInv
.AddItem "Global"
.AddItem "Inventory"
.AddItem "Inventory with Settlement Counsel"
End With
With cboSA
.AddItem "Yes"
.AddItem "No"
.AddItem "Unknown"
End With
txtAction.TabIndex = 0
txtClaimants.TabIndex = 1
cboSales.TabIndex = 2
cboInv.TabIndex = 3
txtDF1.TabIndex = 4
txtDF2.TabIndex = 5
txtDF3.TabIndex = 6
txtDF4.TabIndex = 7
txtDF5.TabIndex = 8
txtSetAmt.TabIndex = 9
txtArchMT.TabIndex = 10
txtProvMT.TabIndex = 11
cboSA.TabIndex = 12
txtSADate.TabIndex = 13
chbLien.TabIndex = 14
chbStanQSF.TabIndex = 15
chbClaims.TabIndex = 16
chbAllocation.TabIndex = 17
chbRelease.TabIndex = 18
chbMDL.TabIndex = 19
chbPFS.TabIndex = 20
chbMedRec.TabIndex = 21
chbBenefits.TabIndex = 22
chbBankruptcy.TabIndex = 23
chbProbate.TabIndex = 24
chbEnhanced1.TabIndex = 25
chbEnhanced2.TabIndex = 26
txtNotes.TabIndex = 27
cmdCancel.TabIndex = 28
cmdUpdate.TabIndex = 29
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdUpdate_Click()
Dim wb As Workbook
Dim wb2 As Workbook
Dim wbname As String
Dim wbname2 As String
Set wb = ActiveWorkbook
Set wb2 = ActiveWorkbook
Application.DisplayAlerts = False
'Make Project Description worksheet active
Worksheets("Project Description").Activate
'Gray out certain input boxes
Call CellLock
'Required cell input
If txtAction.Value = "" Then
MsgBox "You must include the Case/Action name", vbCritical
txtAction.SetFocus
Exit Sub
End If
If txtClaimants.Value = "" Then
MsgBox "You must include the number of claimants participating in the action", vbCritical
txtClaimants.SetFocus
Exit Sub
End If
If cboSales.Value = "" Then
MsgBox "You must include the salesperson for the action", vbCritical
cboSales.SetFocus
Exit Sub
End If
If cboInv.Value = "" Then
MsgBox "You must include the inventory category for this action", vbCritical
cboInv.SetFocus
Exit Sub
End If
If txtDF1.Value = "" Then
MsgBox "You must include the designated firm for this action", vbCritical
txtDF1.SetFocus
Exit Sub
End If
If txtArchMT.Value = "" Then
MsgBox "You must include the Archer mass tort allocation for this action", vbCritical
txtArchMT.SetFocus
Exit Sub
End If
If txtProvMT.Value = "" Then
MsgBox "You must include the Providio mass tort allocation for this action", vbCritical
txtProvMT.SetFocus
Exit Sub
End If
If cboSA.Value = "" Then
MsgBox "You must include whether the Services Agreement has been signed for this action", vbCritical
cboSA.SetFocus
Exit Sub
End If
If cboSA.Value = "Yes" And _
txtSADate.Value = "" Then
MsgBox "You must include the Service Agreement effective date", vbCritical
txtSADate.SetFocus
Exit Sub
End If
'Transfer information
Cells(1, 1).Value = txtAction.Value
Cells(2, 2).Value = txtClaimants.Value
Cells(2, 4).Value = cboSales.Value
Cells(3, 2).Value = cboInv.Value
Cells(4, 2).Value = txtDF1.Value
Cells(5, 2).Value = txtDF2.Value
Cells(6, 2).Value = txtDF3.Value
Cells(7, 2).Value = txtDF4.Value
Cells(8, 2).Value = txtDF5.Value
Cells(3, 4).Value = txtSetAmt.Value
Cells(9, 2).Value = txtArchMT.Value / 100
Cells(9, 4).Value = txtProvMT.Value / 100
Cells(10, 2).Value = cboSA.Value
Cells(10, 4).Value = txtSADate.Value
Cells(1, 7).Value = chbLien.Value
Cells(1, 9).Value = chbStanQSF.Value
Cells(1, 11).Value = chbClaims.Value
Cells(1, 13).Value = chbAllocation.Value
Cells(1, 15).Value = chbRelease.Value
Cells(1, 17).Value = chbMDL.Value
Cells(1, 19).Value = chbPFS.Value
Cells(1, 21).Value = chbMedRec.Value
Cells(1, 23).Value = chbBenefits.Value
Cells(1, 25).Value = chbBankruptcy.Value
Cells(1, 27).Value = chbProbate.Value
Cells(1, 29).Value = chbEnhanced1.Value
Cells(1, 31).Value = chbEnhanced2.Value
Cells(19, 2).Value = txtNotes.Value
'Clear content from unused services and hide the sheets
Call ClearCells
Call HideSheets
ufPDUpdate.Hide
Worksheets("Index").Activate
Cells(2, 1).Select
End Sub
Private Sub ClearCells()
Worksheets("Project Description").Activate
If Cells(1, 7).Value = False Then Cells(12, 2).ClearContents
If Cells(1, 9).Value = False Then Cells(13, 2).ClearContents
If Cells(1, 11).Value = False Then Cells(14, 2).ClearContents
If Cells(1, 13).Value = False Then Cells(15, 2).ClearContents
If Cells(1, 15).Value = False Then Cells(16, 2).ClearContents
If Cells(1, 17).Value = False Then Cells(17, 2).ClearContents
If Cells(1, 19).Value = False Then Cells(12, 3).ClearContents
If Cells(1, 21).Value = False Then Cells(13, 3).ClearContents
If Cells(1, 23).Value = False Then Cells(14, 3).ClearContents
If Cells(1, 25).Value = False Then Cells(15, 3).ClearContents
If Cells(1, 27).Value = False Then Cells(16, 3).ClearContents
If Cells(1, 29).Value = False Then Cells(17, 3).ClearContents
If Cells(1, 31).Value = False Then Cells(18, 3).ClearContents
End Sub
Private Sub HideSheets()
Worksheets("Project Description").Activate
If Cells(1, 7).Value = False Then Worksheets("Lien Resolution").Visible = False
If Cells(1, 9).Value = False Then Worksheets("Standard QSF Services").Visible = False
If Cells(1, 11).Value = False Then Worksheets("Claims Administration").Visible = False
If Cells(1, 13).Value = False Then Worksheets("Allocation Determinations").Visible = False
If Cells(1, 15).Value = False Then Worksheets("Release Administration").Visible = False
If Cells(1, 17).Value = False Then Worksheets("MDL PFS Portal (ASAP-Pre)").Visible = False
If Cells(1, 19).Value = False Then Worksheets("Plaintiff Fact Sheets").Visible = False
If Cells(1, 21).Value = False Then Worksheets("Medical Records Review").Visible = False
If Cells(1, 23).Value = False Then Worksheets("Benefits Analysis").Visible = False
If Cells(1, 25).Value = False Then Worksheets("Bankruptcy Coordination").Visible = False
If Cells(1, 27).Value = False Then Worksheets("Probate Coordination").Visible = False
If Cells(1, 29).Value = False Then Worksheets("Enhanced QSF (Firm directed)").Visible = False
If Cells(1, 31).Value = False Then Worksheets("Enhanced QSF (Full outsource)").Visible = False
End Sub
Private Sub CellLock()
'Dim Inventory As Range
'Inventory = Worksheets("Project Description").Range("B3")
If cboInv.Value = "Inventory" Or cboInv.Value = "Inventory with Settlement Counsel" Then
txtDesFirm2.Locked = True
txtDesFirm2.BackColor = &H80000011
txtDF2.Locked = True
txtDF2.BackColor = &H80000011
txtDesFirm3.Locked = True
txtDesFirm3.BackColor = &H80000011
txtDF3.Locked = True
txtDF3.BackColor = &H80000011
txtDesFirm4.Locked = True
txtDesFirm4.BackColor = &H80000011
txtDF4.Locked = True
txtDF4.BackColor = &H80000011
txtDesFirm5.Locked = True
txtDesFirm5.BackColor = &H80000011
txtDF5.Locked = True
txtDF5.BackColor = &H80000011
End If
End Sub
