Refresh the active Private Sub

B

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
161
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I presently have a number of Private Subs that are triggered by Command Buttons here is an example of two of them

VBA Code: 
'Security Secret needed

If Range("C37") = False And Range("C35") = False And Range("C38") = True And Range("C36") = False Then


    
      
    Range("H13") = Range("AN22")     'type of appointment
    Range("H15") = Range("AX20")     'definition
    
    'Time required by each stakeholder
      
    
    If Range("N35").Value = 0 Then   'security
       Range("L35").Value = 40
    Else
       Range("L35").Value = Range("N35")  'alternate # of days for security
    End If
    
    If Range("N36").Value = 0 Then   'SLE
       Range("L36").Value = 0
    Else
       Range("L36").Value = Range("N36")  'alternate # of days for SLE
    End If
    
    If Range("N37").Value = 0 Then   'Priority clearance
       Range("L37").Value = 0
    Else
       Range("L37").Value = Range("N37")  'alternate # of days for priority
    End If
    
    If Range("N39").Value = 0 Then   'host delegated manager (assignment)
       Range("L39").Value = 0
    Else
       Range("L39").Value = Range("N39")  'alternate # of days pfor host delegated manager (assignment)
    End If
    
    If Range("N40").Value = 0 Then   'hrsc processing - data entry
       Range("L40").Value = 2
    Else
       Range("L40").Value = Range("N40")  'alternate # of days for hrsc - data entry
    End If
    
    'TIMELINE MESSAGE
    
       Range("V14") = Range("AU46")  'security
       Range("V16") = Range("AU31")  'hr request form
       Range("V18") = Range("AU32")  'delegated manager
       Range("V20") = Range("AU41")  'governance
       Range("V22") = Range("AU34")  'finance
       Range("V24") = Range("AU35")  'admin asst
       Range("V26") = Range("AU36")  'hrsc processing
       Range("V28") = Range("AU32")  'delegated manager
       Range("V30") = Range("AU33")  'empoyee signature
       Range("V30").Font.Color = vbBlack
       Range("V32") = Range("AU35")  'admin asst
       Range("V32").Font.Color = vbBlack
       Range("V34") = Range("AU36")  'hrsc processing
       Range("V34").Font.Color = vbBlack
       Range("V36") = Range("AU37")  'pay centre
    
        'Security
    
       If Range("AM3") = True Then     'client selected an alternate date
    Range("AA14").Value = Range("E11") 'Alternate Date as Start Date
    Range("AC14") = Range("AP40")      'Alternate date plus security (reliability)
    Else
    Range("AA14").Value = Range("BS30") 'todays date as Start Date
    Range("AC14") = Range("AP38")       'todays date plus security (reliability)
    End If
       Range("AC14").Font.Color = vbRed 'Date shown in Red
      
       'HR Request Form
    
       If Range("AM3") = True Then      'client selected an alternate date
    Range("AA16").Value = Range("E11")  'Alternate Date as the Start Date
    Else
    Range("AA16").Value = Range("BS30") 'todays date as Start Date
    End If
      
       'Pre-Placement Medical
    
       If Range("AM3") = True And Range("C39") = True Then      'client selected an alternate date
    Range("AA42").Value = Range("AP9")  'alternate date plus pre-placement medical
    End If
    
       If Range("AM3") = False And Range("C39") = True Then      'client did not select an alternate date
    Range("AA42").Value = Range("AO9")  ' date plus pre-placement medical
    End If
    
      
       Range("AA18") = Range("BS32")  'date for delegated manager
       Range("AA20") = Range("BS33")  'date for governance
       Range("AA22") = Range("BS34")  'date for finance
       Range("AA24") = Range("BS35")  'date for admin asst
       Range("AA26") = Range("BS36")  'date for hrsc processing
      
       If Range("AC14").Value > Range("AA26").Value Then 'date for hrsc processing if security received date is greater
       Range("AA26") = Range("AC14")
       Else
       Range("AA26") = Range("BS36")
       End If
      
       If Range("AA42").Value > Range("AA26").Value Then 'date for hrsc processing if pre-placement medical received date is greater
       Range("AA26") = Range("AA42")
       End If
      
       Range("AA28") = Range("BS37")  'date for delegated manager
       Range("AA28").Font.Color = vbBlack
       Range("AA30") = Range("BS38")  'date for employee signature
       Range("AA32") = Range("BS39")  'date for admin asst
       Range("AA34") = Range("BS40")  'date for hrsc processing
       Range("AA36") = Range("BS41")  'date for pay centre
       Range("AA44") = Range("BS42")  'estimated start date
End If


    
    Range("R11") = Range("AA44")
    

ThisWorkbook.Worksheets("Sheet5").Protect ("planner2020")

End If

and

Code: 
'OUTSIDE CFIA - EXTERNAL

'Valid Second Language Results, Priority Clearance and Security Secret needed

If Range("C35") = True And Range("C38") = True And Range("C36") = True And Range("C37") = False Then
      
    Range("H13") = Range("AN22")     'type of appointment
    Range("H15") = Range("AX20")     'definition
    
    'Time required by each stakeholder
      
    
    If Range("N35").Value = 0 Then   'security
       Range("L35").Value = 40
    Else
       Range("L35").Value = Range("N35")  'alternate # of days for security
    End If
    
    If Range("N36").Value = 0 Then   'SLE
       Range("L36").Value = 25
    Else
       Range("L36").Value = Range("N36")  'alternate # of days for SLE
    End If
    
    If Range("N37").Value = 0 Then   'Priority clearance
       Range("L37").Value = 3
    Else
       Range("L37").Value = Range("N37")  'alternate # of days for priority
    End If
    
    If Range("N39").Value = 0 Then   'host delegated manager (assignment)
       Range("L39").Value = 0
    Else
       Range("L39").Value = Range("N39")  'alternate # of days pfor host delegated manager (assignment)
    End If
    
    If Range("N40").Value = 0 Then   'hrsc processing - data entry
       Range("L40").Value = 2
    Else
       Range("L40").Value = Range("N40")  'alternate # of days for hrsc - data entry
    End If
    
    'TIMELINE MESSAGE
 
       Range("V14") = Range("AU46")  'security
       Range("V16") = Range("AU38")  'priority
       Range("V18") = Range("AU42")  'SLE
       Range("V20") = Range("AU31")  'hr request form
       Range("V22") = Range("AU32")  'delegated manager
       Range("V24") = Range("AU41")  'governance
       Range("V26") = Range("AU34")  'finance
       Range("V28") = Range("AU35")  'admin asst
       Range("V30") = Range("AU36")  'hrsc processing
       Range("V32") = Range("AU32")  'delegated manager
       Range("V32").Font.Color = vbBlack
       Range("V34") = Range("AU33")  'employee signature
       Range("V34").Font.Color = vbBlack
       Range("V36") = Range("AU35")  'admin asst
       Range("V36").Font.Color = vbBlack
       Range("V38") = Range("AU36")  'hrsc processing
       Range("V38").Font.Color = vbBlack
       Range("V40") = Range("AU37")  'pay centre
       Range("V40").Font.Color = vbBlack
      
    'TIMELINE DATES
    
       'Security
    
       If Range("AM3") = True Then     'client selected an alternate date
    Range("AA14").Value = Range("E11") 'Alternate Date as Start Date
    Range("AC14") = Range("AP40")      'Alternate date plus security (secret)
    Else
    Range("AA14").Value = Range("BP30") 'todays date as Start Date
    Range("AC14") = Range("AP38")       'todays date plus security (secret)
    End If
       Range("AC14").Font.Color = vbRed 'Date shown in Red
      
    'Priority
    
       If Range("AM3") = True Then     'client selected an alternate date
    Range("AA16").Value = Range("E11") 'Alternate Date as Start Date
    Range("AC16") = Range("AQ39")      'Alternate Date plus Priority
    Else
    Range("AA16").Value = Range("BP30") 'todays date as Start Date
    Range("AC16").Value = Range("AQ37") 'todays date plus Priority
    End If
       Range("AC16").Font.Color = vbRed 'Date shown in Red
      
    'SLE
    
       If Range("AM3") = True Then      'client selected an alternate date
    Range("AA18").Value = Range("E11")  'Alternate Date as Start Date
    Range("AC18") = Range("AQ40")       'Alternate Date plus SLE
    Else
    Range("AA18").Value = Range("BP30") 'todays date as Start Date
    Range("AC18") = Range("AQ38")       'todays date plus SLE
    End If
       Range("AC18").Font.Color = vbRed 'Date shown in Red
      
    'Pre-Placement Medical
    
       If Range("AM3") = True And Range("C39") = True Then      'client selected an alternate date
    Range("AA42").Value = Range("AP9")  'alternate date plus pre-placement medical
    End If
    
       If Range("AM3") = False And Range("C39") = True Then      'client did not select an alternate date
    Range("AA42").Value = Range("AO9")  ' date plus pre-placement medical
    End If
    
    'HR Request Form
    
       If Range("AM3") = True Then      'client selected an alternate date
    Range("AA20").Value = Range("E11")  'Alternate Date as the Start Date
    Else
    Range("AA20").Value = Range("BP30") 'todays date as Start Date
    End If
      
       Range("AA22") = Range("BP34")    'date for delegated manager
       Range("AA24") = Range("BP35")    'date for governance
       Range("AA26") = Range("BP36")    'date for finance
       Range("AA28") = Range("BP37")    'date for admin asst
       Range("AA30") = Range("BP38")    'date for hrsc processing - letter creation
       Range("AA30").Font.Color = vbBlack
      
       If Range("AC14").Value > Range("AA30").Value Then 'date for hrsc processing if security received date is greater
       Range("AA30") = Range("AC14")
       Else
       Range("AA30") = Range("BP38")
       End If
      
       If Range("AC18").Value > Range("AC14").Value Then 'date for hrsc processing if SLE received date is greater
       Range("AA30") = Range("AC18")
       End If
      
       If Range("AA42").Value > Range("AA30").Value Then 'date for hrsc processing if pre-placement medical received date is greater
       Range("AA30") = Range("AA42")
       End If
      
       Range("AA32") = Range("BP39")    'date for delegated manager
       Range("AA32").Font.Color = vbBlack
       Range("AA34") = Range("BP40")    'date for employee signature
       Range("AA34").Font.Color = vbBlack
       Range("AA36") = Range("BP41")    'date for admin asst
       Range("AA36").Font.Color = vbBlack
       Range("AA38") = Range("BP42")    'date for hrsc processing - data entry
       Range("AA40") = Range("BP43")    'date for pay centre
       Range("AA40").Font.Color = vbBlack
       Range("AA44") = Range("BP44")    'estimated start date
      
    End If


both of these option are triggered by the same Command Button: Private Sub CommandButton9_Click () but based on checkboxes selected.... a specific calculation is done

1) Security Secret
2) Valid Second Language Results, Priority Clearance and Security Secret
3) .....there are 6 other scenarios under that command button

what I would like as an option is if the user decides to alter selections that still fall within the parameters under Command Button9 .... they can select a REFRESH button and it recalculates

I have 15 Command buttons so if they activate Command button 1 then the "REFRESH" button is linked to that Command Button and if they activate Command Button 2 then the same "REFRESH"button is linked to command button 2

is that possible

thanks
Dan
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
You must log in or register to reply here.

Similar threads

Z
  • Question Question
VBA: Pass Args back to Sub from PVT Sub
Replies
4
Views
164
Skyybot
Skyybot
S
  • Solved
VBA: Am I double-calling a Sub given nested If statements? Or other inefficiencies?
Replies
8
Views
440
dreid1011
dreid1011
J
  • Question Question
VBA Code for automated actionlog
2
Replies
13
Views
1K
JAZ91
J
A
  • Solved
Macro that creates a word document (and then a PDF) very slow
2
Replies
11
Views
3K
Anthony47
A
A
  • Question Question
Combine text from different cells but apply different formatting
Replies
1
Views
1K
attikuz
A

Forum statistics

Threads
1,223,956
Messages
6,175,616
Members
452,661
Latest member
Nonhle

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