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.

Solved: Run Macro - Selection Off Validation List

Discussion in 'Archive: Business Applications' started by khvnb, Sep 19, 2008.

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

    khvnb Thread Starter

    Joined:
    Sep 19, 2008
    Messages:
    21
    Hey All, Just wanted to put out a general thank you! (y)

    I picked up a VBA Programming book a few days ago and this is probably extremely simple, but I'm having trouble getting a macro to start when a specific selection is made off of a validation list.

    I'm using Excel 2002.

    There is a Validation List/Drop Down Menu in Cell D8. When the term "Res.Mtg." is selected off of that list I'm trying to activate the macro named ResMtg to make Cell E8 equal to Cell F8. The word "Res.Mtg." is pulled from cell AA2237.

    The same effect is desired from all cells of column D.

    This is the code i wrote to hopefully
    1) make the selection "Res.Mtg." from the validation list activate the marco
    2) if the macro is activated by a selection in column D then the cell in column D will always make the cells in Columns E and F the next two to the right.
    3) the two cells to the right (cells in row "x" column E and F) of the active cell (cell in row "x" column D) equal to each other.

    Sub ResMtg()
    Dim Ans As Integer
    Ans = MsgBox("Is the Commitment Exposure Amount = Balance Outstnding Amount?", vbYesNo)
    Select Case Ans
    Case vbYes
    '
    Case vbNo
    '
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveSheet.Paste
    End Select
    End Sub

    So i tried a quite a few variations of the code below to get the code above to run....

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Range("d8").Column
    Case "Res.Mtg."
    Call ResMtg
    Application.EnableEvents = True
    End Select
    End Sub

    I'm not very clear on what exactly the ByVal does, or what values the Select Case Range and Case are looking for.:confused: Help getting this to work would be great but understading this as well would be incredible!!! :D

    Thanks again in advance!!!!
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    As far as I know the ByVal means if the Value in the Cell changes, i.e. not a format.
    Why have a seperate procedure for the RE.Mtg and then "Call" it, why not just have the VBA code as part of the Case Statement.

    The other point is that you do not have to "Select" when transferring value between cells and Zack Barresse, our resident MS Excel guru frowns on it. :D
    You can instead use (assuming your cursor is in Column D)
    ActiveCell.Offset(0, -1).Range("A1") = ActiveCell.Offset(0, 2).Range("A1")
     
  3. khvnb

    khvnb Thread Starter

    Joined:
    Sep 19, 2008
    Messages:
    21
    Thank you and Zack Barresse :p for the advice. I thought that would be more intelligent but didn't know how to write it, and figured copy and paste would get the job done at least.
    I changed it to read;
    ActiveCell.Offset(0, 1).Range("A1") = ActiveCell.Offset(0, 2).Range("A1")
    and that works well.:D But only when i run it manually. :( I really don't understand how to execute the macro when the active cell is changed to the desired selection off of the validation list. The one posted below isn't working and i tried a whole bunch of things. Calling the case or the sub was the most recent trial, all of which have done absolutely nothing. (nothing visable anyway)

    Do you know what code I could used to get it to go?

    Thank you again!!!
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If you can wait until tomorrow and none of the Excel Guys (I am mainly Access) come along I will post something for you tomorrow.
    have you considered using a Combo box to select the validation?
    You can put all the VBA code in to that.
     
  5. khvnb

    khvnb Thread Starter

    Joined:
    Sep 19, 2008
    Messages:
    21
    No, I'm not familiar with that. But i will be by 5pm. :) I'll let you know if it works! I'm still interested in the code if you or anyone else has the time?!

    Thank you sincerely again.
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    So ... you want Sub ResMtg to run when ResMtg is selected from the D8 dropdown.

    Right-click the sheet tab & select View Code from the context-sensitive menu to access the worksheet module.

    Paste in the code below, which utilises the Worksheet_Change event procedure.

    HTH

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$D$8" Then Exit Sub
    If Target = "Res.Mtg." Then
    ResMtg
    End If
    End Sub
     
  7. khvnb

    khvnb Thread Starter

    Joined:
    Sep 19, 2008
    Messages:
    21
    You are awesome!!!!! Thanks!!!!!!!!!!
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I'm pretty sure you have me confused with someone else. :D

    Use Thread Tools at the top of the page to mark this Solved if you wish. :)
     
  9. khvnb

    khvnb Thread Starter

    Joined:
    Sep 19, 2008
    Messages:
    21
    Asolutely not confused about you being awesome! You just saved me from some major headaches.

    Any chance you have time to explain or can direct me to a good source that will explain the pros and cons or differences between putting the sub in a module or directly into worksheet? And or can simply/clarify the ByVal expression?
     
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    What you have is no "ordinary" procedure ("sub" is actually short for "sub procedure"), it's an event procedure. An event procedure is one that runs automatically when a specific event occurs; an event being some "occurrence" such as switching sheets, or changing the current selection, or changing the value of a specific cell as per yours.

    Workbook events go in the ThisWorkbook module, worksheet events go in worksheet modules, that's a given AFAIK.

    "clarify the ByVal"

    I could either type out what it says in this here Excel bible:

    Passing by value, as the term implies, means that you pass the variable's value, rather than the value itself, to the called procedure.

    or I could just say "my clarification is that it works just fine with no explanation". :D

    Not so awesome now eh? :p ;)

    If you really want to get into this, Chip Pearson (for example) is genuinely awesome. HTH :)
     
  11. khvnb

    khvnb Thread Starter

    Joined:
    Sep 19, 2008
    Messages:
    21
    Maybe a little sassy. But you're still A OK in my book. Thank you again for your help. I'll look into your suggested learning material. :cool:
     
  12. 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/751385

  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