Hi all. Been racking my brain for days and have nothing but a headache to show for it.
I have a worksheet that contains data relevant to jobs that are in various stages of completion.
There are 17 jobs (rows) to each sheet.
A button is used to call a userform that allows me to complete various job tasks (Print, Invoice, email, etc). I have named these buttons @CALL1, @CALL2,........@CALL17.
Obviously, If I press @CALL1 it will bring up a userform that allows me to deal with Job #1.
The userform command buttons run macros that contain cell references/Ranges that allow me to prefill templates, documents and emails for printing and saving.
What I would like to do with the cell References & Ranges is have them offset rows depending on what button is pressed.
eg. @CALL1 uses Range("B3") for the job number
@CALL2 uses Range("B6") for the job number
............
@CALL17 uses Range("B51") for the job number
Each Range reference increases rows by 3 for each column. (B3,B6,B9..... D4,D7,D10..... F5,F8,F11, etc, etc)
I have a code that works very well, but it works for sheets, not ranges. I have tried adapting it but don't understand what I need to be doing.
Code for named Buttons and sheets (adapted from code suggested by gjlindn on this forum):
Sub PRINT_FOC()
Dim iSheetNum As Integer
iSheetNum = CInt(Replace(ActiveSheet.Shapes(Application.Caller).Name, "@CALL", ""))
PrintFOC iSheetNum + 1
End Sub
Sub PrintFOC(ByVal iSheetNum As Integer)
Dim lCalc As Long
Dim bScrUpdt As Boolean
Dim bEvents As Boolean
'Speed things up
With Application
lCalc = .Calculation
bScrUpdt = .ScreenUpdating
bEvents = .EnableEvents
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
'Do what you want here when button is clicked
Sheets(iSheetNum).PrintOut Copies:=1
'Restore settings
With Application
.Calculation = lCalc
.ScreenUpdating = bScrUpdt
.EnableEvents = bEvents
.Calculation = xlCalculationAutomatic
End With
End Sub
This works very well for printing sheet 2-18, depending on what button is pressed.
I have attached the beginning of the code, that calls the userform , when the button is pressed.
Is there a way that the above button code can be modified or adapted to update ranges?
Public pwjnum As Integer ' B3, B6, B9, B12, etc = JOB #
Public super As String ' I4, I7, I10, I13, etc = Supervisor Name
Public buildcode As String ' B4, B7, B10, B13, etc = Builder Code
Sub CallJobFrm01()
'Ranges used when @CALL1 is pressed
'Ranges should offset by 3 rows down (in all cases) for @CALL2
'Ranges should offset by 6 rows down for @CALL3
' etc, etc
pwjnum = Worksheets("CHECKLIST").Range("B3").Value
super = Worksheets("CHECKLIST").Range("I4").Value
buildcode = Worksheets("CHECKLIST").Range("B4").Value
With JobFrm01
.Caption = "JOB #" & pwjnum
.CommandButton01.Caption = "PRINT JOB PACK #" & pwjnum
.CommandButton02.Caption = "PRINT F.O.C #" & pwjnum
.CommandButton03.Caption = "PRINT E.B.H R.A #" & pwjnum
.CommandButton04.Caption = "PRINT P.W R.A #" & pwjnum
.CommandButton05.Caption = "COMPILE INVOICE #" & pwjnum
.CommandButton06.Caption = "COMPILE VARIATION #" & pwjnum
.CommandButton07.Caption = "REQUEST VARIATION #" & pwjnum
.CommandButton11.Caption = "EMAIL " & super & " ABOUT THIS JOB?"
.CommandButton12.Caption = "EMAIL " & buildcode & " ACCOUNTS DEPT ABOUT THIS JOB?"
.CommandButton14.Caption = "COMPILE 'Blank' INVOICE FOR JOB #" & pwjnum
.CommandButton15.Caption = "COMPILE,SAVE & EMAIL INVOICE & RISK ASSESSMENT FOR JOB #" & pwjnum
End With
JobFrm01.Show
End Sub
Variables have been declared public because they are used in several macros inside this UserForm and other related userforms.
Regards,
Martin
Bookmarks