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):

Please Login or Register  to view this content.

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?

Please Login or Register  to view this content.

Variables have been declared public because they are used in several macros inside this UserForm and other related userforms.

Regards,

Martin

You can use TopLeftCell to get the row for the button that has been clicked.

You can then use that for your ranges.

Martin

The TopLeftCell property of a button returns, well, the top left cell of the button.

So if the button covered B3:B5 it would return B3.

I was thinking you could use that to identify the row of the button and subsequently the ranges you need.

I'll need to have another look at your code and check the file.

This is kind of what I mean.

Please Login or Register  to view this content.

You can use rngJob, with Offset, to get the appropriate data for the job linked to the button that was pressed.

Hope that makes some kind of sense.

Thanks Norie. Works brilliantly and makes sense!

Cheers for the help.

Regards,
Martin