Help to disable popup calendar

[Geordie]

Hi guys

I need some help to disable a popup calendar. I have it set to popup when certain cells are selected. Problem is, I have another macro on the same sheet that resets these cells to a default value. When this reset happens, the calendar pops up.
How do I prevent the popup for the reset function?

Thanks in advance for the help.

[p45cal]

Whats the code that pops up the calendar look like?

I might use a global variable, for example Blocked As Boolean, set to True when the restting macro starts, and resets Blocked to False at the end of that macro. Then around the code which pops up the calendar have the likes of:
If not Blocked then
'do the popup calendar stuff
End if

[Geordie]

Hi p45cal,

OK, what I know about code is dangerous, I am a complete novice to VB, so not sure exactly what code you want.... but here's what I've done so far....

code from UserForm1 in the Forms folder
Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat = "dd - mm - yyyy"
End Sub

Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub


code from one of my sheets in Microsoft Excel Objects folder
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address = "$B$20" Or ActiveCell.Address = "$E$12" Then
ShowIt
End If
End Sub


code from the module in Modules folder (the macro that opens the popup)
Sub ShowIt()
UserForm1.Show
End Sub


this is the code from the module that resets the cells to default values in the sheet
Sub TCHOME()

Range("A6:G6").Select
Selection.ClearContents
Range("A15:J15").Select
Selection.ClearContents
Range("A16:J16").Select
Selection.ClearContents
Range("A17:J17").Select
Selection.ClearContents
Range("A18:J18").Select
Selection.ClearContents
Range("I6:J6").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=RC[24]"
Range("I9:J9").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=R[-6]C[24]"
Range("I12:J12").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "NIL"
Range("C9:E9").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "='Vessel Particulars'!R[-7]C[7]"
Range("A9").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=R[1]C[35]"
Range("G12").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("A12").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=R[-10]C[35]"
Range("C12").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("E12").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("B20:C20").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("F20:G20").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=R[-11]C[-3]"
Range("I20:J20").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[-18]C[21],"" "",R[-18]C[20],"" "",R[-18]C[18])"
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("HOME").Select
End Sub

Now after being at work today, I have stumbled across a new hurdle. I have created this spreadsheet on my laptop in excel 2007 (win 7) but my work pc is excel 2003 (win xp) the work pc does not have the calendar add-in, but I found it has a Microsoft Month add-in that seems to be very similar to my popup - can I alter this existing code for the excel 2003 version?

Hope the above code is what you were after.

Thanks again for your help

[p45cal]

This is easier than I thought, you're using a selection event and restricting it to two cells, B20 and E12.
Your TCHOME code selects E12 (not B20) so smallest change to make this work is change the 3 three lines of code in TCHOME which currently read:
Range("E12").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=TODAY()" to te single line:
Range("E12").FormulaR1C1 = "=TODAY()" thus avoiding selecting E12, and that's it.


However…
TCHOME could be a lot slicker:
Sub TCHOME2()
Range("$A$6:$G$6,$J$6,$D$9:$E$9,$J$9,$J$12,$C$20,$G$20,$I$20:$J$20,$A$15:$J$18").ClearContents
Range("I6").FormulaR1C1 = "=RC[24]"
Range("I9").FormulaR1C1 = "=R[-6]C[24]"
Range("I12").FormulaR1C1 = "NIL"
Range("C9").FormulaR1C1 = "='Vessel Particulars'!R[-7]C[7]"
Range("A9").FormulaR1C1 = "=R[1]C[35]"
Range("G12").FormulaR1C1 = "=R[-3]C"
Range("A12").FormulaR1C1 = "=R[-10]C[35]"
Range("C12").FormulaR1C1 = "=R[-3]C"
Range("E12").FormulaR1C1 = "=TODAY()"
Range("B20").FormulaR1C1 = "=TODAY()"
Range("F20").FormulaR1C1 = "=R[-11]C[-3]"
Range("I20").FormulaR1C1 = "=CONCATENATE(R[-18]C[21],"" "",R[-18]C[20],"" "",R[-18]C[18])"
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("HOME").Select
End Sub
Also it's a bit more conventional to use Target instead of Activecell.address in the Selection_Change event:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$20" Or Target.Address = "$E$12" Then ShowIt
End Sub

[Geordie]

Hey p45cal

Thanks for that, after some very minor adjustments (because some cells are merged) your code works perfectly. Such a huge help for me.

Do you think you could help me with the excel 2003 / 2007 problem ?
Now after being at work today, I have stumbled across a new hurdle. I have created this spreadsheet on my laptop in excel 2007 (win 7) but my work pc is excel 2003 (win xp) the work pc does not have the calendar add-in, but I found it has a Microsoft Month add-in that seems to be very similar to my popup - can I alter this existing code for the excel 2003 version?
Also, I am unable to install the add-on since the work pc is locked out, so can't install any new software on it.

I really appreciate this

[p45cal]

Can you post a version of the file on the interweb somewhere, say box.net?
Otherwise PM me.

[Geordie]

Uhmm, sorry - not quite sure what file you want here?

My excel spreadsheet file - or - the Microsoft Month add-on file?

[p45cal]

the file with all this code in! Hopefully an Excel 2007 file. Then I can look into what can be done in 2003 - I have both versions of Excel.

[Geordie]

OK, I've uploaded the file to box.net but I cannot post a link to it due to only having 4 posts and cannot PM you for the same reason.

I would prefer to PM you the link as this file contains some sensitive / personal information of other people and I need to respect their privacy.

So I need 6 more posts to do this.