Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run Code/Macro (Excel 2000)

    Good Afternoon,

    OK...so I'm stupid...if this were Access, I'd know what to do....but it isn't....at any rate, I want to run either a macro or code when the user exits a cell. Can someone PLEASE tell me how to do this?

    Below is the code I would like to run once the user Selects an answer in cell D105:

    Private Sub NumOfDays()

    If Not Intersect(Target, Range("D105")) Is Nothing And Range("D105") = "Yes" Then
    Range("D106").Select
    myInput = InputBox("Please enter the No. of Therapy Visits.")
    For Each myCell In Selection
    myCell = myInput
    Next myCell

    Else

    If Not Intersect(Target, Range("D105")) Is Nothing And Range("D105") = "No" Then
    Range("D107").Select
    End If
    End If
    End Sub
    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run Code/Macro (Excel 2000)

    This type of code belongs in the Worksheet_Change or Worksheet_SelectionChange event in the worksheet module. You can't just use Target in any old macro.

    The code looks strange: in the If part you select one cell (D106), then loop through all cells in the selection. The loop will only set D106, since that is the selection. Did you intend to do something else?

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Code/Macro (Excel 2000)

    Hans,

    Probably just messed up....trying to combine pieces of two codes together.....

    I already have a Worksheet_Change and Worksheet_SelectionChange in my worksheet....can I have more than 1? How do I accomplish what I want to do if not?

    I'm clueless here....basically, if the user selects Yes from a dropdown in D105 I want a Input box to appear asking them the No. of visits and input that value in D106 and move to D107, if the user's response is NO then I want them to move to D107.

    Once again, Thanks for your help
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run Code/Macro (Excel 2000)

    So you want to move to D107 in all cases? If so, you can add this code to the existing code in the Worksheet_Change or Worksheet_SelectionChange event:

    If Target = Range("D105") Then
    If Range("D105") = "Yes" Then
    Range("D106") = InputBox("Please enter the No. of Therapy Visits.")
    End If
    Range("D107").Select
    End If

    You cannot have two separate Worksheet_Change events for the same worksheet, but you can combine different pieces of code in the one event procedure.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Code/Macro (Excel 2000)

    As always Hans, Thank you!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Code/Macro (Excel 2000)

    Hans: Isn't your first if statement going to compare the value in the Target cell with the value in D105? That could give an incorrect result if Target is not D105 but has the same value in it. I think that what you wanted was:

    <pre> If Not Intersect(Target, ActiveSheet.Range("D105")) Is Nothing Then
    </pre>

    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run Code/Macro (Excel 2000)

    Thanks. I didn't want to use Intersect here, for I don't think that Roberta wants to select cell D107 if the user changes a larger area containing D105. But you are correct that my attempt will compare the values, and that is not what I intended. Perhaps this:

    If Target.Address = Range("D105").Address Then

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Code/Macro (Excel 2000)

    But if D105 is included in the Target range, then it was changed and I think that she would want to do the rest.

    BTW, I don't think that you can use the Selection Change event. The Target will be the cell you are changing to and I haven't found a way to find what cell you are coming from.
    Legare Coleman

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run Code/Macro (Excel 2000)

    OK, so:

    - use the Worksheet_Change event, not Worksheet_SelectionChange
    - test as proposed by you (and as Roberta originally had, from <post#=376158>post 376158</post#>):

    If Not Intersect(Target, ActiveSheet.Range("D105")) Is Nothing Then

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Code/Macro (Excel 2000)

    That's what I would do.
    Legare Coleman

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Code/Macro (Excel 2000)

    Good Morning,

    Thank you both.....I was just about to make a post saying my code:

    If Not Intersect(Target, Range("D105")) Is Nothing And Range("D105") = "Yes" Then
    Range("D106").Select
    Range("D106") = InputBox("Please enter the No. of Therapy Visits")
    Else

    If Not Intersect(Target, Range("D105")) Is Nothing And Range("D105") = "No" Then
    Range("D107").Select
    End If
    End If
    I had this in the "Worksheet_SelectionChange"

    wasn't working ..... when I found your replies here.....

    Your suggestions work perfectly....Again .... thanks to the both of you....you're great....have a nice day!!!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •