1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

double case or if statement branch

Discussion in 'Business Applications' started by spooky1, Mar 13, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    Trying to create a sheet whereby I can direct traffic or run macros from 2 different cells (not at the same time) see file.

    So if 1 is entered into cell C5 then it runs macro a, if it is 2 it runs macro b etc etc
    &

    So if "fred" is entered into cell e5 then it runs macro barks, if it is "son" it runs macro "zoo" etc etc

    Would like to be able to do this on more that 2 cells if possible.
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    I'll take a look later today. I'm sure you can with a selct case statement or similar
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    Change your code to this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub

    Select Case ActiveCell.Row
    Case Is = 5
    MsgBox Cells(ActiveCell.Row, ActiveCell.Column).Value
    End Select

    End Sub
     
  4. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    copied code into module, click 5 in a cell nothing happened. Created a macro and put it in

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub

    Select Case ActiveCell.Row
    Case Is = 5
    MsgBox Cells(ActiveCell.Row, ActiveCell.Column).Value
    a
    End Select

    End Sub

    What am I missing? Range not specified? I f so where do I specify range
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    I think you didn't paste the code in the correct module, it should be in the page's module, not like a macro,

    The code is based on actions on row 5
    I have attached my version of your sample.
    Let me konw if it works now and, are the macro's enabled?
     

    Attached Files:

  6. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    The sample file u provided works brilliantly. U guys are geniuses!
    Small point, is there any way to limit it to specified cells? If anything is entered in row 5 any column it
    operates.

    I could just keep that row clear but if it is not too difficult it would be better limited to specific cells.
    eg C5 & G5

    Thank u for your time.
     
  7. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub

    Select Case ActiveCell.Row
    Case Is = 5
    MsgBox Cells(ActiveCell.Row, ActiveCell.Column).Value

    'Case "How"
    'MsgBox ("eggs") or run a mcro


    End Select

    End Sub

    'the idea was that when how is selected from C5 then it would run a macro and if C5 was are it would run a different macro and so forth. That way macros could be run from the data validation box rather
    than messy icons.
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    Well just put that in the code
    I'm off to bed now but will look to it tomorrow
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/909698

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice