I have 2 questions:
1. I have constructed a Macro workbook divided by three tabs: “IDL," “HSN" and “ List”. The List tab, outlines all the drop down menu options based on Categories (Apparel, Bath Bedding, Décor, Furniture etc.). For example if the user chooses “Apparel” in Column E, the dependent dropdown menu in Column F shows:
Blouse
Blazer
Pant
This is possible by using data validation (=indirect(E2)). Column E is formatted in a way that you could make multiple selections such as "Apparel" and " Décor," which would be stacked versus separated by a comma. I have adhered to the instructions found on Contextures (http://www.contextures.com/excel-data-validation-multiple.html) regarding selecting multiple items from an Excel data validation list. The VBA code used is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& " " & newVal
' NOTE: you can use a line break,
' instead of a comma
' Target.Value = oldVal _
' & Chr(10) & newVal
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
__________________________
CHALLENGE: If I make multiple selections in Column E such as "Apparel" and "Décor" the data validation formula (=indirect(E1)) only recognizes the initial selection and not the second or third. So you are left in Column F with only the Apparel dependent list to select from versus having BOTH the Apparel and Décor dependent drop down lists. How can I resolve this?
2. How do I add a pop up calendar that activates when a specific column (i.e. Col. A) is selected, then disappears after the users has selected their date?
• I have created a Macro Calendar called ‘IDL_HSN Database_Draft.xlsm’! Show Calendar. Show Calendar. But don't want the user to have to click on an icon in order to enter dates in column A.
Please advise.
Sincerely,
Maria
1. I have constructed a Macro workbook divided by three tabs: “IDL," “HSN" and “ List”. The List tab, outlines all the drop down menu options based on Categories (Apparel, Bath Bedding, Décor, Furniture etc.). For example if the user chooses “Apparel” in Column E, the dependent dropdown menu in Column F shows:
Blouse
Blazer
Pant
This is possible by using data validation (=indirect(E2)). Column E is formatted in a way that you could make multiple selections such as "Apparel" and " Décor," which would be stacked versus separated by a comma. I have adhered to the instructions found on Contextures (http://www.contextures.com/excel-data-validation-multiple.html) regarding selecting multiple items from an Excel data validation list. The VBA code used is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& " " & newVal
' NOTE: you can use a line break,
' instead of a comma
' Target.Value = oldVal _
' & Chr(10) & newVal
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
__________________________
CHALLENGE: If I make multiple selections in Column E such as "Apparel" and "Décor" the data validation formula (=indirect(E1)) only recognizes the initial selection and not the second or third. So you are left in Column F with only the Apparel dependent list to select from versus having BOTH the Apparel and Décor dependent drop down lists. How can I resolve this?
2. How do I add a pop up calendar that activates when a specific column (i.e. Col. A) is selected, then disappears after the users has selected their date?
• I have created a Macro Calendar called ‘IDL_HSN Database_Draft.xlsm’! Show Calendar. Show Calendar. But don't want the user to have to click on an icon in order to enter dates in column A.
Please advise.
Sincerely,
Maria
