[khvnb]

Hey All, Just wanted to put out a general thank you!

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. Help getting this to work would be great but understading this as well would be incredible!!!

Thanks again in advance!!!!

[OBP]

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.
You can instead use (assuming your cursor is in Column D)
ActiveCell.Offset(0, -1).Range("A1") = ActiveCell.Offset(0, 2).Range("A1")

[khvnb]

Thank you and Zack Barresse 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. 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!!!

[OBP]

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.

[khvnb]

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.

[bomb #21]

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

[khvnb]

You are awesome!!!!! Thanks!!!!!!!!!!

[bomb #21]

Originally Posted by khvnb

You are awesome!!!!! Thanks!!!!!!!!!!

I'm pretty sure you have me confused with someone else.

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

[khvnb]

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?

[bomb #21]

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".

Not so awesome now eh?

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

[khvnb]

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.