if worksheet visible - calendar form

[JK]

I'm using Martin Green's frmCalendar form in Excel 03. The way it works, if
you click the calendar icon and select a date - the date will be inserted
into the cell that happens to be selected at the time. I want the date to go
into a specified cell - meaning, it doesn't matter what cells is selected
when the calendar form is opened and a date selected.

I was able to make this work by defining the cell in the VBA. However, I use
the calendar code on more than one worksheet. I need to do an if visible or
if enabled statement before the calendar code fires.

This is what I'm using now:

If IsDate(Worksheets("Shift Sheet").Cells(6, 6).Value) Then
Calendar1.Value = DateValue(Worksheets("Shift Sheet").Cells(6,
6).Value)
Else
Calendar1.Value = Date
End If


I want to insert something in front of this that says:

If Worksheet "2nd Shift Regular" is Visible or Enabled run above code Else
if Worksheet "2nd Shift Overtime is Visible or Enabled run above code.

Any help would be greatly appreciated.

Regards,
Jason

 

[Patrick Molloy]

your first block of code puts the date from the cell into the calendar control.


maybe...

If Worksheets("2nd Shift regular").Visible = xlSheetVisible Then
Range("A1").Value = Calendar1.Value
End If

 

[JK]

For some reason - this does not work.

If Worksheets("Shift Sheet").Visible = xlSheetVisible Then
MsgBox "Visible!", vbOKOnly
Else
MsgBox "Not Visible!", vbOkOnly
End If

It doesn't matter what worksheet is actually visible - I always get the
"Visible!" msgbox.

If I could get this to work - I'd be golden.

 

[Patrick Molloy]

do yuo mean visble as in its the active sheet or visible in terms of its
property?

the visible property is eithe Visible, Hidden or very Hidden
its only visible sheets that you can tab through
with the visible sheets, the one that you see isn't "visible", its teh
Activesheet
I think this is really what you mean

If Worksheets("Sheet1").Name = ActiveSheet.Name Then
MsgBox "Visible!", vbOKOnly
Else
MsgBox "Not Visible!", vbOKOnly
End If

 

[JK]

Thank you so much - worked like a charm!

Jason

do yuo mean visble as in its the active sheet or visible in terms of its
property?

the visible property is eithe Visible, Hidden or very Hidden
its only visible sheets that you can tab through
with the visible sheets, the one that you see isn't "visible", its teh
Activesheet
I think this is really what you mean

If Worksheets("Sheet1").Name = ActiveSheet.Name Then
MsgBox "Visible!", vbOKOnly
Else
MsgBox "Not Visible!", vbOKOnly
End If