Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

doesn't work code based on hidden sheet

0

Hello,

I  try  to  make  macro   . shouldn't  work  macro Sub v() if  there  is  no  sheet name RUN  at  all  in  file  and  if  the  sheet name  is  existed  but  is  hidden  then  should  not  work  the  code 

otherwise  should  be  work 

it  gives  mismatch in this  line 

If Sheets(ws).Visible = xlVeryHidden Then
Sub run_macro()
Dim ws As Worksheet
Set ws = Sheets("RUN")
If Sheets(ws).Visible = xlVeryHidden Then
If Evaluate("ISREF('" & "Run" & "'!A1)") = True Then

    Call v '! Or Macro Name
Else
    MsgBox " sorry you can't running this macro, should check admin,please.", vbCritical
End If
End If
End Sub
Sub v()
Range("a2").Value = "hello"
End Sub

any  idea  to  achieve  it, please?

Answer
Discuss

Answers

0
Selected Answer

Ali

You need to

  1. comment out the Set line (which will cause an error if such a named sheet doesn't exist) and
  2. swap your tests to check using ISREF first then, once you know the RUN sheet exists, name it and test for visibility

Changes in bold (with comments) below:

Sub run_macro()
Dim ws As Worksheet
' don't use this line (or may cause error)
'Set ws = Sheets("RUN")

'see if Run sheet exists
If Evaluate("ISREF('" & "Run" & "'!A1)") = True Then
    ' check if Run is hidden by VBA
    If Not Worksheets("Run").Visible = xlVeryHidden Then
        ' if it's NOT hidden by VBA, call other macro and quit
        Call v '! Or Macro Name
        Exit Sub
    Else
        'if it IS hidden by VBA, do nothing but...
    End If
End If

'... clear A2 and tell user if there's no Run sheet or it's hidden
Range("A2").Value = ""
MsgBox "Sorry you can't run this macro, please check with admin.", vbCritical

End Sub


Sub v()
Range("A2").Value = "Unhidden Run sheet found"
End Sub

I've added comments in case I did not understand correctly your intent of your question/ discussion point (and so you can swap outcomes if needed)

Remember that you are only testing for xlVeryHidden (which can only set by VBA)- this test will not work with sheets which are manually hidden by a user.

Hope this works for you.

Discuss

Discussion

p.s. I changed the syntax of your message box output (to more standard UK/ American syntax).
John_Ru (rep: 6142) Jun 4, '22 at 10:33 am
thanks   for  your  answer , but  I    think  there  is  problem  .  if  there  is   no  created  sheet name   RUN    should  show  the  message  but   just   doesn't  call  macro  without   pops up  the  message .  I tested  your two both  answers  . it  gives  the  same  result  .
Ali M (rep: 28) Jun 4, '22 at 3:38 pm
Ali. Sorry it's just a matter of making the right IF and ELSE statements but I struggled to understand your question. Please edit it to state clearly the outcomes needed for no RUN sheet, hidden RUN and visible RUN sheets. I'll then correct my answer (tomorrow probably). 
John_Ru (rep: 6142) Jun 4, '22 at 5:22 pm
Ali

You didn't reply to my point above but I modified my answer to what I think you want. To save my time, I removed the alternative looping solution.
John_Ru (rep: 6142) Jun 6, '22 at 9:49 am
John
You didn't reply to my point
 
 I  no  know  what  I  have  to  add  more  details   if  my  question  is  not  clear. forgive  me !   actually I  was  confused  what  I  should  add more  details  to  understand me   , that's  why  I  don't  replyed you.
thanks  for  your  answering works  greatly !
Ali M (rep: 28) Jun 6, '22 at 10:10 am
Glad it worked. (Note that to answer my points, you could have used the EDIT button on your original question - but no need now).  Thanks for selecting my answer Ali. 
John_Ru (rep: 6142) Jun 6, '22 at 10:39 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login