Results 1 to 6 of 6

Named Button to Offset Referenced Ranges in workbook when clicked

Threaded View

Lungfish Named Button to Offset... 12-31-2012, 04:02 AM
Norie You can use TopLeftCell to... 12-31-2012, 04:13 AM
Lungfish Re: Named Button to Offset... 12-31-2012, 04:28 AM
Norie Martin The TopLeftCell... 12-31-2012, 05:08 AM
Norie Re: Named Button to Offset... 12-31-2012, 05:27 AM
Lungfish Re: Named Button to Offset... 01-01-2013, 06:52 PM
  1. #1
    Registered User
    Join Date
    09-18-2008
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    66

    Named Button to Offset Referenced Ranges in workbook when clicked

    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
    Last edited by Lungfish; 01-02-2013 at 03:44 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1