Help with VBA

[APML]

Hi all, I'm not very good at VBA, and had help achieving the following code from MrExcel. My problem is I only want it to work on the workbook called "Journal", but it
seems to work on all workbooks, hoping someone can advise on how to modify

This part is in "This Workbook" (Visual basic) in "Journal" workbook

Private Sub Workbook_Open()
Call Maximize_Workspace(True)
Application.OnKey "^z", "UnMaximize_Workspace"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Disable_Key
End Sub

This is the macro
Sub Test__Maximize_Workspace()
Call Maximize_Workspace(True)
End Sub
Sub Maximize_Workspace(trueOrFalse As Boolean)
If trueOrFalse = True Then
Application.DisplayStatusBar = False
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
Call Show_Top_Ribbon(False)
Else
Application.DisplayStatusBar = True
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayGridlines = True
Call Show_Top_Ribbon(True)
End If
End Sub
Sub Test__Show_Top_Ribbon()
Call Show_Top_Ribbon(True)
End Sub
Sub Show_Top_Ribbon(hideUnhide As Boolean)
If hideUnhide = True Then
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
Else
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
End If
End Sub
Sub Disable_Key()
Application.OnKey "^z", "Do_Nothing"
End Sub
Sub Do_Nothing()
'Do nothing
End Sub
Sub UnMaximize_Workspace()
Call Maximize_Workspace(False)
End Sub

 

[Logit]

Suggestions :

Application.OnKey only for one spreadsheet

Heres a way around it:

Private Sub Workbook_Open()
    Application.OnKey "~", "Call_Hyper"
    Application.OnKey "{ENTER}", "Call_Hyper"
End Sub

In a module place this:

Sub Call_Hyper
If Activeworkbook.Name = "NAME OF YOUR WORKBOOK" Then
Call Set_Hyper
Else
Exit Sub
End If

End Sub

Or -

[Solved]-OnKey for one worksheet only-VBA Excel

 

[APML]

Firstly, thanks for your help. So I'm not very good at VBA, in fact, I'm really poor at it.
So I'm not sure how I'm meant to be altering my code or where I should be putting your suggestion, can you pls advise?

 

[Alex Blakenburg]

I don't understand. Are the macros in the workbook Journal ? Some of your other threads seem to indicate they are.
If they are why don't you just assign a shortcut key to the macro.

Also I hope you are the only one using the workbook because any other users are going to hate you for highjacking the shortcut key for "undo" (ctrl+z)

 

[APML]

I don't understand. Are the macros in the workbook Journal ? Some of your other threads seem to indicate they are.
If they are why don't you just assign a shortcut key to the macro.

Also I hope you are the only one using the workbook because any other users are going to hate you for highjacking the shortcut key for "undo" (ctrl+z)

Yes, the macros are in the workbook Journal. I actually never considered a shortcut key, it does make sense but the worksheet will be used
by others, so was wanting the macro to run on opening the workbook. I'm just very poor at VBA so wasn't understanding how to actually make the above changes that you suggested

And yes I do realise ctrl + z is a bad mistake so I'm going to change the letter.

 

[Alex Blakenburg]

Try allocating a shortcut key using ctrl+shift+SomeLetter.

See if this makes it run just on the Journal workbook,

Sub Test__Maximize_Workspace()
    If ThisWorkbook.Name = ActiveWorkbook.Name Then
        Call Maximize_Workspace(False)
    End If
End Sub

 

[Joe4]

Not sure why you started a new thread for this, when I already responsed to your question yesterday, and you never followed up on it:

VBA _ Only use macros in this workbook

Hi all, I'm looking for VBA so when my workbook named "Journal" is opened it only uses the macros in that workbook. I'm. not sure but I think excel by default uses macros in all open workbooks which is a pain for me. All help appreciated

www.mrexcel.com

In the future, please do not do that. Please keep it all to one thread, as Fluff explained.