Macro for List Options:

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Macro for List Options:

Post by vilas desai »

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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro for List Options:

Post by HansV »

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

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Macro for List Options:

Post by vilas desai »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro for List Options:

Post by HansV »

Hi Vilas,

Our email notification system is not perfect. It's best to check the forum itself from time to time.
Best wishes,
Hans

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Macro for List Options:

Post by vilas desai »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro for List Options:

Post by HansV »

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

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Macro for List Options:

Post by vilas desai »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro for List Options:

Post by HansV »

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

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Macro for List Options:

Post by vilas desai »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro for List Options:

Post by HansV »

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

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Macro for List Options:

Post by vilas desai »

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

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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro for List Options:

Post by HansV »

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.
Best wishes,
Hans

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Macro for List Options:

Post by vilas desai »

Oh yes Thanks.