locked
Select Case Statements - With a Worksheet Range RRS feed

  • Question

  • Hi friends,

     

    A potential question, I am not sure how to do this or if it's possible.

    Currently I have to type all the cell addresses as below into a Select Case statement, and the addresses change quite a lot so I have to change them and  its really hard to maintain.

     

    Some of the strings are long  :(

    It works with the Worksheet Selection change event.

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    Select Case Target.Address 'Case 1 : Cell Addresses Case "$AC$500", "$AD$505", "$CA$1000", "$CB$1005", "$DY$1500", "$DZ$1505", "$FW$2000", "$FX$2005", _ "$HV$2505", "$JS$3000", "$JT$3005", "$LQ$3500", "$LR$3505", "$NO$4000", "$PM$4500", "$NP$4005", "$RK$5000", "$RN$6000", "$VJ$8000", "$ZF$10000", "$ADB$12000", "$AGX$14000", "$AKT$16000", "$AOP$18000", "$ASL$20000", "$AWH$22000", "$BAD$24000", "$BDZ$26000", "$BHV$28000" ' Call TaskA Case "$AK$509" Call TaskB Case "$AK$500" Call TaskC

    What I would like to try to do is have all the cell addresses listed as above.

    Case "ActiveSheet.Range("A2:A29")   ' << Which stores the values of the First Case Cell adresses

    Call Task A

     

    Which looks a lot more easy on the eyes, and easy for me to mainitain is this possible?

     

    So when I click on any of the cells in Case 1 cell addresses in the actual spreadsheet as the active cell -  it will do the task as it was doing before.

     

    I couldn’t find anything to illustrate this idea on google

    Any ideas welcome

     


    Cheers Dan :)



    • Edited by Dan_CS Tuesday, November 29, 2016 12:32 PM Code error
    Tuesday, November 29, 2016 12:28 PM

Answers

  • Dear Dan,

    Following would do the trick

    Step 1. Add a Module in your Excel VBE Project and then Paste the following code 

    Note: I am assuming that TaskA, TaskB etc. these macros are also there in the same module.

    Function callTheMacro(selectedCell As Range)
    Dim iRow As Integer
    iRow = 2 'as your data starts from row 2
        While Range("A" & iRow).Value <> ""
            If Not Intersect(selectedCell, Range(Range("A" & iRow).Value)) Is Nothing Then
            
            ' If your macros for TasA, B etc is in a sheet then uncomment the below
            ' statement, it will do the magic and comment the line below - "Application.Run....
            
                'CallByName Sheet1, Range("A" & iRow).Offset(0, 1).Value, VbMethod
                
            ' if your macro for Task A, B etc is written in this module, same module then
            ' use below
                Application.Run Range("A" & iRow).Offset(0, 1).Value
                Exit Function
            End If
            iRow = iRow + 1
        Wend
    End Function
    
    Sub TaskA()
        MsgBox "this is Task A"
    End Sub
    Sub TaskB()
        MsgBox "this is Task B"
    End Sub

    Now go to SelectionChnage event of your WorkSheet and paste the following code:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        callTheMacro Target
    End Sub

    Let me know if this helps..


    Vish Mishra

    • Marked as answer by Dan_CS Tuesday, November 29, 2016 2:01 PM
    Tuesday, November 29, 2016 1:42 PM

All replies

  • Dear Dan,

    Following would do the trick

    Step 1. Add a Module in your Excel VBE Project and then Paste the following code 

    Note: I am assuming that TaskA, TaskB etc. these macros are also there in the same module.

    Function callTheMacro(selectedCell As Range)
    Dim iRow As Integer
    iRow = 2 'as your data starts from row 2
        While Range("A" & iRow).Value <> ""
            If Not Intersect(selectedCell, Range(Range("A" & iRow).Value)) Is Nothing Then
            
            ' If your macros for TasA, B etc is in a sheet then uncomment the below
            ' statement, it will do the magic and comment the line below - "Application.Run....
            
                'CallByName Sheet1, Range("A" & iRow).Offset(0, 1).Value, VbMethod
                
            ' if your macro for Task A, B etc is written in this module, same module then
            ' use below
                Application.Run Range("A" & iRow).Offset(0, 1).Value
                Exit Function
            End If
            iRow = iRow + 1
        Wend
    End Function
    
    Sub TaskA()
        MsgBox "this is Task A"
    End Sub
    Sub TaskB()
        MsgBox "this is Task B"
    End Sub

    Now go to SelectionChnage event of your WorkSheet and paste the following code:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        callTheMacro Target
    End Sub

    Let me know if this helps..


    Vish Mishra

    • Marked as answer by Dan_CS Tuesday, November 29, 2016 2:01 PM
    Tuesday, November 29, 2016 1:42 PM
  •  

    Hello Vish,

     

    You are a star! 

     

    I really didn’t know that you could do this.

     

    I was guessing but yesterday a 5 minute job took me 20 minutes, because I had to keep going to the spreadsheet cell

    Then the code module and rechecking   - it was a nightmare :(

     

    I also tried to do something with the intersect method but that was abit of a mess as well.

     

    Well the idea seems simple enough but I always get stuck with the syntax.

     

    I am so happy I asked - thank you so much for helping  and have a great day now !

     

    🙂


    Cheers Dan :)

    Tuesday, November 29, 2016 2:01 PM