2

I want to auto-run this private sub when opening the excel sheet.

I tried using Private Sub Workbook_Open() method but as the first private sub does not have a name, it does not work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet: Set ws = Sheets("Budget- Reporting")
    If Range("W6").Value = 0 Then
        HideFG
    Else
        HideF
    End If
End Sub

Sub HideF()
'
' HideF Macro
'

'
    For i = 1 To ActiveSheet.Shapes.Count
    ActiveSheet.Shapes(i).Visible = msoTrue
    Next i
    ActiveSheet.Shapes.Range(Array("F")).Visible = msoFalse
    Application.CommandBars("Selection").Visible = False
End Sub

Sub HideFG()
'
' HideFG Macro
'

'
    For i = 1 To ActiveSheet.Shapes.Count
    ActiveSheet.Shapes(i).Visible = msoTrue
    Next i
    ActiveSheet.Shapes.Range(Array("FG")).Visible = msoFalse
    Application.CommandBars("Selection").Visible = False
End Sub

I hope that it automatically checks cell W16 when opening the excel file and carries on with HideF macro or HideFG macro. Currently, the two macros run once you actual type on the cell after opening the file.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
MooChang Park
  • 81
  • 5
  • This is not a private sub, this is a worksheet change `Event` it is triggered everytime something is changed on that particular sheet. – Damian Sep 13 '19 at 10:15

2 Answers2

0

the easiest way is to use the default Module "ThisWorkbook" which gets executed when opening the excel file. You can find it within your VBA Project Explorer on the left side of the window. Just take the sub you want to execute and copy it into the space.

Its explained in great detail here: https://support.office.com/en-us/article/automatically-run-a-macro-when-opening-a-workbook-1e55959b-e077-4c88-a696-c3017600db44

If it is necessary for your usecase this can help you to call a private sub:

Private Sub PrivateCallDemo()
'Module2
Application.Run "Module1.Worksheet_Change"
End Sub

This way your actual Sub could stay in another Module.

G.M
  • 345
  • 3
  • 22
  • You very welcome, FreeSoftwareServers said, please accept and vote for helpful answers (we all want more rep ;) ) – G.M Sep 13 '19 at 11:44
0

You have a few problems. First you don't want Worksheet_Change(ByVal Target As Range) as that is for events triggers on changes to the workbook, you want Workbook_Open(). This gets stored under ThisWorkbook not a separate module/sheet.

Here is working code, I commented out your ws declaration for testing.

Private Sub Workbook_Open()
'Dim ws As Worksheet: Set ws = Sheets("Budget- Reporting")
    If Range("W6").Value = 0 Then
        HideFG
    Else
        HideF
    End If
End Sub

Sub HideF()
 MsgBox "HideF"
End Sub

Sub HideFG()
 MsgBox "HideFG"
End Sub

Here is a screenshot of my editor.

enter image description here

G.M. posted a great resource as well found here --> https://support.office.com/en-us/article/automatically-run-a-macro-when-opening-a-workbook-1e55959b-e077-4c88-a696-c3017600db44

I just put the modules in the same spot for the screenshot, but you can put them separately and still use the Call HideFG method if you want to store your modules separately from the workbook_open event as I would want to.

FreeSoftwareServers
  • 2,271
  • 1
  • 33
  • 57
  • You are a genius :) Thank you so much for the help! It is solved now. Have a great day! – MooChang Park Sep 13 '19 at 11:39
  • Please vote/accept an answer so others know your issue is resolved and users gain rep for helping :). Glad it's fixed. – FreeSoftwareServers Sep 13 '19 at 11:39
  • Bad advice: "Finally, you want to use `Call HideFG` not just `HideFG`". Quite frankly that does nothing and advising people to use obsolete code snippets is just simply irresponsible! – AJD Sep 13 '19 at 21:53
  • Seems like its not big deal to use call https://stackoverflow.com/q/2573597/5079799 – FreeSoftwareServers Sep 14 '19 at 00:06
  • @FreeSoftwareServers: the use of "Call" is often brought up here - and referencing a 9 year old post may not provide the most recent commentary. https://github.com/rubberduck-vba/Rubberduck/issues/34 mentions it, and https://stackoverflow.com/questions/2573597/should-i-use-call-keyword-in-vb-vba (also 9 years old) provides a more in-depth discussion. Just because one can, doesn't mean one should. But the real kicker in your answer here is where you said "you want to use `Call` […]" because the current code was perfectly fine and did not require any finessing. – AJD Sep 14 '19 at 05:16
  • [continued …] The internet is awash with over simplified VBA coding examples (I know because I have used lots of them myself) which introduce unwanted habits that will eventually cause grief. If you read the RubberDuck blogs and understand the issues with "UserForm1.Show", you can then also understand that just because some popular says to do code some particular way, it does not mean that it is right. – AJD Sep 14 '19 at 05:19
  • @FreeSoftwareServers: you also don't know that it was me that provided the downvote, you are guessing unless I otherwise confirm it. Remember, downvotes are part of a peer quality control process and are nothing personal (at least that is the way it should be). DVs only reflect the usefulness of the answers in the context of the question asked. – AJD Sep 14 '19 at 05:22
  • Thanks @AJD for really making me feel unwelcome and stupid for making a mistake saying to use call... Very constructive criticism with proactive results. – FreeSoftwareServers Sep 17 '19 at 12:40