Userform formulas not working if updated

V

Vonsteiner

New Member
Joined
Apr 14, 2014
Messages
45
Office Version
  1. 365
Platform
  1. 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:
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 Sub

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.

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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
You must log in or register to reply here.

Similar threads

I
  • Question Question
Listbox columns dont alter when i set the width advice please
Replies
7
Views
135
Dan_W
D
J
  • Question Question
Userform inconsistent responses
Replies
1
Views
218
Candyman8019
C
K
  • Question Question
How to shorten vba code without any problems
Replies
1
Views
288
kamranyd
K
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
212
ipbr21054
I
I
  • Question Question
Userform does not show leading 0 for telephone number
Replies
1
Views
272
ipbr21054
I

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top