Macro for List Options:
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Macro for List Options:
Dear Experts,
Here is what want to achieve:
for a ws named "aaa" If a range "zzz"has a string "xxx" then I would like to have a macro which gives me "select Options" in the range "yyy" and this range will have an indication( like a filter symbol) to show one has to select an option. The default option will be the first in the list of options. Please see example as attached
There would be many such instances / examples in the same worksheet.
Thanks and best regards
Vilas Desai
Here is what want to achieve:
for a ws named "aaa" If a range "zzz"has a string "xxx" then I would like to have a macro which gives me "select Options" in the range "yyy" and this range will have an indication( like a filter symbol) to show one has to select an option. The default option will be the first in the list of options. Please see example as attached
There would be many such instances / examples in the same worksheet.
Thanks and best regards
Vilas Desai
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for List Options:
Here is something to start with:
Code: Select all
Sub CreateDV()
Dim wsh As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Set wsh = Worksheets("PT")
Set rng1 = wsh.Range("K1")
Set rng2 = wsh.Range("K21")
rng2.Validation.Delete
If rng1.Value = "Status" Then
With rng2.Validation
.Add Type:=xlValidateList, Formula1:="In Progress,Issued for Review,Issued for Purchase"
.InputTitle = "Select option"
.InputMessage = "Please select an item from the list"
.ErrorTitle = "Incorrect input"
.ErrorMessage = "You may only select items from the drop down list!"
End With
rng2.Value = "In Progress"
End If
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Macro for List Options:
Thanks Hans. I will try it out.
I observe on last few occasions I don't get an email notification that my request is responded. Whom do I talk about this to? Please advise.
Best regards
Vilas Desai
I observe on last few occasions I don't get an email notification that my request is responded. Whom do I talk about this to? Please advise.
Best regards
Vilas Desai
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for List Options:
Hi Vilas,
Our email notification system is not perfect. It's best to check the forum itself from time to time.
Our email notification system is not perfect. It's best to check the forum itself from time to time.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Macro for List Options:
No problem Hans. Thanks.
Continuing with this solution, can you please help me with
a. Macro running at the instance of opening the worksheet
b. Using "Currently open wsh" instead of checking for the name of the worksheet
Continuing with this solution, can you please help me with
a. Macro running at the instance of opening the worksheet
b. Using "Currently open wsh" instead of checking for the name of the worksheet
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for List Options:
Copy the following code into the ThisWorkbook module:
Code: Select all
Private Sub Workbook_Open()
Dim wsh As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Set wsh = Activesheet ' instead of Worksheets("PT")
Set rng1 = wsh.Range("K1")
Set rng2 = wsh.Range("K21")
rng2.Validation.Delete
If rng1.Value = "Status" Then
With rng2.Validation
.Add Type:=xlValidateList, Formula1:="In Progress,Issued for Review,Issued for Purchase"
.InputTitle = "Select option"
.InputMessage = "Please select an item from the list"
.ErrorTitle = "Incorrect input"
.ErrorMessage = "You may only select items from the drop down list!"
End With
rng2.Value = "In Progress"
End If
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Macro for List Options:
Hello Hans,
Working fine. Thanks.
If I have to add more such conditions for the same workbook and same worksheet,
If K1 = Status, then K21=Option List
If K2 = Logic, then K22= Boolean, etc
should I keep adding them ?
Thanks and best regards
Vilas Desai
Working fine. Thanks.
If I have to add more such conditions for the same workbook and same worksheet,
If K1 = Status, then K21=Option List
If K2 = Logic, then K22= Boolean, etc
should I keep adding them ?
Thanks and best regards
Vilas Desai
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for List Options:
It could look like this:
Code: Select all
Private Sub Workbook_Open()
Dim wsh As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Set wsh = ActiveSheet ' instead of Worksheets("PT")
Set rng1 = wsh.Range("K1")
Select Case rng1.Value
Case "Status"
Set rng2 = wsh.Range("K21")
With rng2.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="In Progress,Issued for Review,Issued for Purchase"
.InputTitle = "Select option"
.InputMessage = "Please select an item from the list"
.ErrorTitle = "Incorrect input"
.ErrorMessage = "You may only select items from the drop down list!"
End With
rng2.Value = "In Progress"
Case "Logic"
Set rng2 = wsh.Range("K21")
With rng2.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="TRUE,FALSE"
.InputTitle = "Select option"
.InputMessage = "Please select an item from the list"
.ErrorTitle = "Incorrect input"
.ErrorMessage = "You may only select items from the drop down list!"
End With
rng2.Value = True
Case "..."
' ...
End Select
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Macro for List Options:
Hello Hans
I may not have communicated it correctly, but I was trying to ask this:
- If K1 = Status, then K21 = List options (a,b,c,d) and
- If K2 = Logic, then K22 = List options (e,d,f)
your code mentions
- If K1 = Status, then K21 = List options (a,b,c,d) and
- If K2 = Logic, then K22 = List options (e,d,f)
I apologise if I was''nt clear.
Thanks and best regards
Vilas Desai
I may not have communicated it correctly, but I was trying to ask this:
- If K1 = Status, then K21 = List options (a,b,c,d) and
- If K2 = Logic, then K22 = List options (e,d,f)
your code mentions
- If K1 = Status, then K21 = List options (a,b,c,d) and
- If K2 = Logic, then K22 = List options (e,d,f)
I apologise if I was''nt clear.
Thanks and best regards
Vilas Desai
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for List Options:
Sorry, you did mention K1, K2.
Code: Select all
Private Sub Workbook_Open()
Dim wsh As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Set wsh = ActiveSheet ' instead of Worksheets("PT")
Set rng1 = wsh.Range("K1")
If rng1.Value = "Status" Then
Set rng2 = wsh.Range("K21")
With rng2.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="In Progress,Issued for Review,Issued for Purchase"
.InputTitle = "Select option"
.InputMessage = "Please select an item from the list"
.ErrorTitle = "Incorrect input"
.ErrorMessage = "You may only select items from the drop down list!"
End With
rng2.Value = "In Progress"
End If
Set rng1 = wsh.Range("K2")
If rng1.Value = "Logic" Then
Set rng2 = wsh.Range("K22")
With rng2.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="d,e,f"
.InputTitle = "Select option"
.InputMessage = "Please select an item from the list"
.ErrorTitle = "Incorrect input"
.ErrorMessage = "You may only select items from the drop down list!"
End With
rng2.Value = "d"
End If
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Macro for List Options:
Thanks Hans.
One last question under the same topic.
as another use case, I have no conditions, and just want to add options in a Range
I tried as below but it gives me error 1004:
The code in bold and italics is commented.
I have also enclosed an attachment with an example of what I want.
Thanks and regards
Vilas Desai
One last question under the same topic.
as another use case, I have no conditions, and just want to add options in a Range
I tried as below but it gives me error 1004:
The code in bold and italics is commented.
I have also enclosed an attachment with an example of what I want.
Thanks and regards
Vilas Desai
Code: Select all
Private Sub Workbook_Open()
Dim wsh As Worksheet
[b]Dim rng1 As Range[/b]
Dim rng2 As Range
Set wsh = ActiveSheet ' instead of Worksheets("PT")
[b][i] Set rng1 = wsh.Range("K1")[/i][/b]
[b][i] If rng1.Value = "Status" Then[/i][/b]
Set rng2 = wsh.Range("K21")
With rng2.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="In Progress,Issued for Review,Issued for Purchase"
.InputTitle = "Select option"
.InputMessage = "Please select an item from the list"
.ErrorTitle = "Incorrect input"
.ErrorMessage = "You may only select items from the drop down list!"
End With
rng2.Value = "In Progress"
[b][i] End If[/i][/b]
Set rng1 = wsh.Range("K2")
If rng1.Value = "Logic" Then
Set rng2 = wsh.Range("K22")
With rng2.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="d,e,f"
.InputTitle = "Select option"
.InputMessage = "Please select an item from the list"
.ErrorTitle = "Incorrect input"
.ErrorMessage = "You may only select items from the drop down list!"
End With
rng2.Value = "d"
End If
End Sub
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for List Options:
There shouldn't any need for this code. Once you have set the conditional formatting rule, it will be saved with the workbook, so you don't have to create it each time the workbook is opened.
The code works without error for me, by the way.
The code works without error for me, by the way.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Macro for List Options:
Oh yes Thanks.