Worksheet shortcuts.

[trevolly]

Hi all.

I recently made a Daily log for work and part of this was a Hanover Sheet that could be filled in and with the help of some v clever members of this site, have a vba code in the worksheet that when actioned copies the Handover worksheet in to the next shifts Daily Log. This enables the Handover sheet to be saved from shift to shift, allowing vital info for each shift to be ready for the day / night.

This works well apart from……. On the Handover Sheet I have made some “jump to” shortcuts by putting a button in and then recording a macro allowing the user to jump to a certain part of the Handover sheet.

Macro consists of …..
Sub Marshalling()
‘
‘Marshalling Macro
‘
Range (“A106”). Select
End Sub

This works apart from when the Handover sheet is copied to the next shift. Then when these “jump to” shortcuts are pressed then the original Daily log workbook opens in the background (I suppose because that’s where the original Handover sheet and the macro shortcuts were recorded).

Is there any way to make one of these “jump to “ shortcuts / buttons on the Handover sheet without it referencing the original workbook and opening it?

 

[My Aswer Is This]

The script your showing does not refer to any particular Workbook or Worksheet.
So why pressing a button that only says: Range (“A106”). Select would cause another workbook to open is something I do not believe could happen. There must be something else in the script.

 

[Fluff]

It sounds as though you are using Form Control buttons. If you use ActiveX buttons instead I think you should be ok.

 

[trevolly]

Hi @Fluff. I inserted a shape (rectangle) and then recorded a macro, and with the mouse selected a cell I wanted to jump to and then stopped recording the macro. Then I applied the macro to the shape I inserted.

@My Aswer Is This Okay…. Not sure why I’d take the effort to write this if it’s not doing it….(sorry if u don’t believe me). I’m more than happy to record a video if it doing it and upload a link if it’s allowed. I’ve uploaded a screenshot of the vba code of the four “jump to” shortcuts that all do it.

 

[Fluff]

As I said, if you switch to ActiveX controls, I'm pretty sure you will be ok.

 

[trevolly]

Thank you

 

[Fluff]

You're welcome & thanks for the feedback.

 

[My Aswer Is This]

Glad to see you received a answer.
If you would like you can always use a double click event to run a script so no buttons would be needed.
Double click on Range("G1") and your script would run for example.
If your interested let me know.

 

[Fluff]

Another option would be to use Hyperlinks.

 

[trevolly]

Ahhh that seems easier @Fluff. Thank you - why is excel so confusing!!