Vbs calendar select change by designated target cell

[DenniBrink]

Hello everyone! I need assistance in adapting the following vbscript:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'check cells for desired format to trigger the calendarfrm.show routine
'otherwise exit the sub
Dim DateFormats, DF
DateFormats = Array("m/d/yy;@", "mmmm d yyyy")
For Each DF In DateFormats
If DF = Target.NumberFormat Then
If CalendarFrm.HelpLabel.Caption <> "" Then
CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
Else: CalendarFrm.Height = 191
CalendarFrm.Show
End If
End If
Next
End Sub

Currently, the pop-up calendar is activated by any cell on the worksheet that has the defined DateFormat, DF. I desire the target to only activate when the Range("B3") is selected.

 

[mumps]

I have to agree with BQardi. His suggestion and my suggestion work in the same fashion. Basically, if you select any cell other than B3, the macro shouldn't run.

 

[DenniBrink]

This is why I suspect that something else is going on, probably with other code you haven't shared.

A simple example would prove this:
Create a new fresh, blank workbook
Put this code in the worksheets selectionchange event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$3" Then
    MsgBox "Only B3 will activate this"
End If
End Sub

Select, for instance, C21, and notice that nothing happens. Same if you select C44.
ONLY if you select B3, will the MsgBox popup.
This is what my solution does for your code. If calenderfrm is shown when you select C21, then the only conclusion would be that something else is going on, and I would need more information to "fix" it...

As you suggested I created a new workbook and in the Visual Basic Editor pasted the recommended code in ThisWorkBook. Yes, the Range("B3") when selected does display the MsgBox "Only B3 will activate this". However, when I take it to the next logical step for the calendarfrm.show code you initially suggested the calendar does not appear.

I have reviewed the calendarfrm.show code which is resides in a Class Module. The only other code you are alluding to is script that builds the calendar.

 

[DenniBrink]

FYI. The code for the pop-up calendar was obtained from the Internet. The date picker script was created by Ioannis-Ntizoglou.

 

[BQardi]

FYI. The code for the pop-up calendar was obtained from the Internet. The date picker script was created by Ioannis-Ntizoglou.

Aaaah that helped a lot. There is also a Worksheet_SelectionChange event in the Class Module. You should change that as well...
The code should be an exact replica of the Worksheet_SelectionChange event from your main Worksheet...

 

[DenniBrink]

I added the If Target.Address = "$B$3" Then to both the Class Module and Calendar Worksheet. Still the script does not perform as desire.

 

[BQardi]

Well it did at my end with "Date-Picker-by-Ioannis-Ntizoglou.xlsm".
Have you added some other code elsewhere?
This may be a stupid question, but I have to ask: did you change the cell with the date-formatting to B3 (it was originally in C3)?
Did you copy the whole code to the Class module or did you manually insert the lines. If so could it be that you accidentally placed some of the code wrongfully, f. ex. if the "End If" is placed before "Next" the code will run, but not work when selecting B3 ("End If" should be after "Next").
Again stupid, but I have to ask...

 

[DenniBrink]

I finally figure out the problem. The date format, how the cell is actually formatted, is how the calendarfrm.show is triggered. I changed the cell format for the reference table so that the calendarfrm would not be enabled when any of the cells in the table are selected. With only cell "B3" with the correct format (mmmm d, yyyy) the date picker activation is now restricted.

 

[BQardi]

Great... Glad you found the solution to your problem...

 

[DenniBrink]

You were right from the beginning! If Target Address = "B3" Then, there must be something else. The something else was DF = dateformat and target.dateformat (mmmm d, yyyy). We have to remove the DF trigger to change how the calendarfrm. show is activated. All the best to you.