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.

 

[BQardi]

Very simple:

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

 

[DenniBrink]

Very simple:

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

The offered solution does not work! When I select any cell with the DF format the pop-up calendar activates. I desire the calendar to appear only when cell B3 with the DF format is selected.

 

[mumps]

Place this line of code at the beginning of your macro:

If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub

 

[BQardi]

I do not understand what you want then.
The code I provided:

If Target.Address = "$B$3" Then
    ...
End If

Will make sure that the code inside it, will ONLY fire if cell B3 is selected...

When I select any cell with the DF format the pop-up calendar activates

If that happens with my code, something else is going on.
Do you have other code elsewhere?
For example in ThisWorkbook:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    ...
End Sub

Maybe I am misunderstanding something here...
If so, can you explain in another way. Maybe with some visual examples?

 

[DenniBrink]

Place this line of code at the beginning of your macro:

If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub

Does not work!

 

[DenniBrink]

What do you not understand? I have shared with the code of the pop up calendar. You do understand the calendarfrm.show is determined by the data format array. On the worksheet there are numerous cells that have the DF array data format. It is my wish that only the Range("B3") with the data format array be the only cell that activates the calendarfrm.show.

 

[BQardi]

What I don't understand is, when you say:

It is my wish that only the Range("B3") with the data format array be the only cell that activates the calendarfrm.show.

and I give you a piece of code that will ONLY activate when you click on Range("B3") and only shows the calenderfrm if Range("B3") has the format in the array, then it is not what you want?

What do you want then?

Are you saying that calenderfrm is still "popping up" with my code, if you click a cell that is not Range("B3")?
Are you saying that calenderfrm should popup when you click any cell but only if Range("B3") has the format?
Are you saying that calenderfrm should popup when you click Range("B3") regardless of the format of Range("B3")?
Are you saying that calenderfrm should popup when you click any cell that has the same format as Range("B3")?
Are you saying something else?

Be clear...

Remember, you are the ONLY one who knows what your project is used for (and how it is used), so what happens in your code (or what your intentions are), are very clear to you. They are not so clear for "outsiders", aka helpers here at MrExcel.
So the better you are at explaining your poblem, the more and better help you get.

Explain it as you would explain it to a 3 year old:

If you want a 3 year old to go pour up milk for him/herself you don't say:
Go get the milk and pour it in a glass and drink.

You would rather say:
Go to the fridge, look at the second shelf from the bottom and find the bottle with the white stuff inside.
Open the bottle by unscrewing the lid.
Find a glass in the cupboard just besides the fridge.
Place the glass on the table.
Take the bottle and hold both hands on the bottle.
Gently pour the contents into the glass.
Don't pour to fast or you'll spill, and only fill half the glass.
Now put the bottle aside and you can drink your milk.

My point being, be specific and as detailoriented as you can to avoid questions as the ones I have asked...

 

[DenniBrink]

What I don't understand is, when you say:

and I give you a piece of code that will ONLY activate when you click on Range("B3") and only shows the calenderfrm if Range("B3") has the format in the array, then it is not what you want?

What do you want then?

Are you saying that calenderfrm is still "popping up" with my code, if you click a cell that is not Range("B3")? YES!
Are you saying that calenderfrm should popup when you click any cell but only if Range("B3") has the format?
Are you saying that calenderfrm should popup when you click Range("B3") regardless of the format of Range("B3")?
Are you saying that calenderfrm should popup when you click any cell that has the same format as Range("B3")?
Are you saying something else?

Be clear...

Remember, you are the ONLY one who knows what your project is used for (and how it is used), so what happens in your code (or what your intentions are), are very clear to you. They are not so clear for "outsiders", aka helpers here at MrExcel.
So the better you are at explaining your poblem, the more and better help you get.

Explain it as you would explain it to a 3 year old:

If you want a 3 year old to go pour up milk for him/herself you don't say:
Go get the milk and pour it in a glass and drink.

You would rather say:
Go to the fridge, look at the second shelf from the bottom and find the bottle with the white stuff inside.
Open the bottle by unscrewing the lid.
Find a glass in the cupboard just besides the fridge.
Place the glass on the table.
Take the bottle and hold both hands on the bottle.
Gently pour the contents into the glass.
Don't pour to fast or you'll spill, and only fill half the glass.
Now put the bottle aside and you can drink your milk.

My point being, be specific and as detailoriented as you can to avoid questions as the ones I have asked...

I desire the calendarfrm to activate only when Range("B3") is selected. Presently the way the code is written the calendarfrm. show is activated when any cell as the DF array format. I want the target to be exclusively defined and restrict the activation of the calendarfrm.show only to Range("B3").

If cell C21 has the DF and it is selected calendarfrm is triggered. If cell C44 is selected and it also has the target.dateformat then calendarfrm is triggered. Your suggested code does zero modification to the calendarfrm.show script.

 

[BQardi]

Your suggested code does zero modification to the calendarfrm.show script.

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...