| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
if worksheet visible - calendar form
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if worksheet visible - calendar form
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if worksheet visible - calendar form
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" wrote: 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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if worksheet visible - calendar form
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" wrote: 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" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if worksheet visible - calendar form
Thank you so much - worked like a charm!
Jason "Patrick Molloy" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Making a form visible | Excel Discussion (Misc queries) | |||
| Label on User Form visible | Excel Programming | |||
| Call up form calendar form VBA | Excel Programming | |||
| Copy form field value to another form (calendar) | Excel Programming | |||
| Worksheet has to set to visible as it is not visible after saving and closing Excel by VB. | Excel Programming | |||
Linear Mode
