pops up message when run the userform if specific sheet is hidden

[KalilMe]

hello

I have many sheets are hidden except sheet MAIN to run userform from of it . when I run the userform and the sheet DATA is hidden , then should pops up messge " threre is missed data" and close the userform , and if the sheet DATA is not hidden when run the userform should keep showing .
and if try up to 3 times and three times shows the message then should pops up message" sorry run out your tries" and close the file

thanks

 

[Micron]

try this in userform initialize event

Private Sub UserForm_Initialize()
If FormShowCount = 3 Then
     MsgBox "A message here"
     Exit Sub
End If

If Sheets("DATA").Visible = False Then
     MsgBox "Your message here"
     Unload Me
End If

FormShowCount = FormShowCount + 1

End Sub

You would need a module level variable:
Public FormShowCount As Integer

in a standard module.

 

[KalilMe]

thanks . I don't see the line to close the file after three times and gives error .
about this

Public FormShowCount As Integer

are you sure about put in standard module?

 

[Micron]

thanks . I don't see the line to close the file after three times and gives error .
about this

If FormShowCount = 3 Then
If you want after 3 then change to = 4 OR >= 3

are you sure about put in standard module?

Put what? Public FormShowCount As Integer? Then yes.

and gives error

That tells me nothing. That code raises an error? Then what is the message and which line gets highlighted?

 

[KalilMe]

I use F8 and shows error as Pic in the end code END SUB

 

[Micron]

I'll bet it's because the form closes before the opening code completes. Will figure out if that's the case and how to fix.

 

[Micron]

That is the problem - the event that opens the form complains because the form no longer exists. I presume you're using a sheet activeX button to open userform. This would be one way to eliminate the error message that results:

Private Sub CommandButton1_Click()

On Error GoTo errHandler
UserForm.Show
Exit Sub

errHandler:
If Err.Number = 91 Then
     Resume Next
Else
     MsgBox "Error " & Err.Number & ": " & Err.Description
End If

End Sub

 

[KalilMe]

actually I no know how your solution works
may you see my file , guide me what's my mistake , please?
LABLE.xlsm

 

[Micron]

You named your userform as userform1. Also, you put the public variable in two times. The public variable in the userform code should not be there. I changed =3 to >=2 because FormShowCount begins at zero, so 3 tries is 0,1,2. I changed the messages to make it easier for me to see what was happening. Try the following:

Private Sub CommandButton1_Click()

On Error GoTo errHandler
UserForm1.Show '<<changed to your userform name
Exit Sub

errHandler:
If Err.Number = 91 Then
     Resume Next
Else
     MsgBox "Error " & Err.Number & ": " & Err.Description
End If

End Sub

Private Sub UserForm_Initialize() '<<do not change to userform1 here
If FormShowCount >= 2 Then
     MsgBox "You have reached the maximum tries allowed."
     Unload Me
     Exit Sub
End If

If Sheets("DATA").Visible = False Then
     MsgBox "Cannot proceed if Data sheet is hidden"
     Unload Me
End If

FormShowCount = FormShowCount + 1

End Sub

 

[KalilMe]

thanks
as I said

and if try up to 3 times and three times shows the message then should pops up message" sorry run out your tries" and close the file

should close the file after three times when run the userform