Microsoft Excel - is there a possible way to assign a dropdown menu a target adress?

Asked By ozzy efendi on 26-May-11 09:24 AM
hi, everyone.. i am a newbie in Excel VBA macro codes, and i' ve stucked in a macro code.

I' ve set a form control combobox (drop_down menu) and with index formula i've tied this combobox to range(B56:B52) and celllink (G53).  Every item in combobox has different style or number format. (For example "A" item datas are %percent, and "B" item datas are "currency". When i work with data validation, because of i can assign a target adress, i can change the data formats when i change item in data validation (not: i tied data validation with drop down also to allow datas change). However, i can't assign a target adress into dropdown menu. So when i change items in drop down , the datas for the listitems change but data formats don't.

Cell for Data validation is $F$56
Range that i've written Index formula in : "B56:B82"
Index formula reference value (cell link): $G$53
Data Validation Source: $D$55:$D$59
Drop down menu input range: $D$55:$D$59

Codes:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$F$56" Then
   Application.EnableEvents = False
   If Target.Value = "A" Then
Range("$G$53").Value = "1"
Range("B56:B82").NumberFormat = "#,##0.00 TL"
   ElseIf Target.Value = "B" Then
Range("$G$53").Value = "2"
Range("B56:B82").NumberFormat = "#,##0.00 TL"
   ElseIf Target.Value = "C" Then
Range("$G$53").Value = "3"
Range("B56:B82").NumberFormat = "#,##0.00 TL"
   ElseIf Target.Value = "D" Then
Range("$G$53").Value = "4"
Range("B56:B82").Style = "Percent"
   ElseIf Target.Value = "E" Then
Range("$G$53").Value = "5"
Range("B56:B82").Style = "Comma"
   End If
   Application.EnableEvents = True
 End If
End Sub 


I am working on it for nearly 3 days but still can't success. Can anyone help me in this? 

Thanks.
wally eye replied to ozzy efendi on 26-May-11 01:16 PM
I must be missing something, because what your code works just fine for me.  I've attached a sample sheet with your code, does it do what you would expect?

Dropdown Target.zip
ozzy efendi replied to wally eye on 26-May-11 01:48 PM
wally eye, thanks for the response. yes, as you've said it is working fine, but only with data validation. My purpose ise to do exact the same things for drop down menu, not data validation list. But because of i can't define a target value to drop down list, i can't do the things what i am doing in data validation. I wonder if there is a way to trigger macro when i change items from drop down menu. I've updated the excel file just like my original file. You can look at he excel file. Thanks again.

wally eye replied to ozzy efendi on 26-May-11 02:36 PM
Since you have it tied to $G$53, and it updates it with an index into your drop-down, you can just use $G$53.  I put a new module in that mimics your original one:

Dropdown Target2.zip
ozzy efendi replied to wally eye on 26-May-11 02:51 PM
Works great, thank you very much!!