Macro VBA, Drop down IF statement
Code ------------------- Sub DropDown1_Change() ' ' DropDown1_Change Macro ' Macro recorded 02/11/2005 by 96bst01 If C4 = "1" Then Range("E4").Select Selection.NumberFormat = "0%" ElseIf C4 = "2" Then Range("E4").Select Selection.NumberFormat = "£#,##0.00" End If End Sub ------------------- I've got a spreadsheet, it has an ID, either 1 or 2, in cell C4. Thi is changes when I change my dropdown (These are linked up to anothe table, with ID 1 and 2). I'm trying to say, if the value in C4 is 1, format E4 to a percentage Else if the value in C4 is 2, I want it to format as currency. As I am a programmer myself, mainly PHP, nothing like this, understand the structure and know how to debug things. I think th problem is coming from it not understanding what C4 is. Do I need t define it? Thanks -- Matt_2 ----------------------------------------------------------------------- Matt_2K's Profile: http://www.excelforum.com/member.php...nfo&userid=617 View this thread: http://www.excelforum.com/showthread.php?threadid=48102 |
Macro VBA, Drop down IF statement
Sub change_test () if range("C4").value = "1" then range("E4").numberformat = "0.00%" if range("C4").value = "2" then range("E4").numberformat = "$0.00" end if end if end sub that should work.. -- dok11 ----------------------------------------------------------------------- dok112's Profile: http://www.excelforum.com/member.php...fo&userid=1058 View this thread: http://www.excelforum.com/showthread.php?threadid=48102 |
Macro VBA, Drop down IF statement
Sub change_test () if range("C4").value = "1" then range("E4").numberformat = "0.00%" if range("C4").value = "2" then range("E4").numberformat = "$0.00" end if end if end sub that should work.. -- dok11 ----------------------------------------------------------------------- dok112's Profile: http://www.excelforum.com/member.php...fo&userid=1058 View this thread: http://www.excelforum.com/showthread.php?threadid=48102 |
Macro VBA, Drop down IF statement
Thanks. I modified my code a bit using this: Code ------------------- Sub DropDown1_Change() ' ' DropDown1_Change Macro ' Macro recorded 02/11/2005 by 96bst01 If Range("C4").Value = "1" Then Range("E4").Select Selection.NumberFormat = "0%" ElseIf Range("C4").Value = "2" Then Range("E4").Select Selection.NumberFormat = "£#,##0.00" End If End Sub ------------------- Now, this code works fine for one row, but can I have this on multipl rows? E.g. having a drop down box which is linked to a cell reference, BUT need the C4 and E4 to change depending on the row and cell link. Ho can I achieve this in VBA -- Matt_2 ----------------------------------------------------------------------- Matt_2K's Profile: http://www.excelforum.com/member.php...nfo&userid=617 View this thread: http://www.excelforum.com/showthread.php?threadid=48102 |
Macro VBA, Drop down IF statement
You would need to loop the command. This will loop through the comman until there is no longer a value in column C. Sub test() Dim i As Integer i = 1 Do While Cells(i, 3).Value "0" If cells(i, 3).Value = "1" Then cells(i, 5).Select Selection.NumberFormat = "0%" ElseIf cells(i, 3).Value = "2" Then cells(i, 5).Select Selection.NumberFormat = "£#,##0.00" End If i = i + 1 Loop End Sub Matt_2K Wrote: Thanks. I modified my code a bit using this: Code ------------------- Sub DropDown1_Change() ' ' DropDown1_Change Macro ' Macro recorded 02/11/2005 by 96bst01 If Range("C4").Value = "1" Then Range("E4").Select Selection.NumberFormat = "0%" ElseIf Range("C4").Value = "2" Then Range("E4").Select Selection.NumberFormat = "£#,##0.00" End If End Sub ------------------- Now, this code works fine for one row, but can I have this o multiple rows? E.g. having a drop down box which is linked to a cell reference, BU i need the C4 and E4 to change depending on the row and cell link How can I achieve this in VBA -- dok11 ----------------------------------------------------------------------- dok112's Profile: http://www.excelforum.com/member.php...fo&userid=1058 View this thread: http://www.excelforum.com/showthread.php?threadid=48102 |
Macro VBA, Drop down IF statement
Thanks for your reply. I have drop downs linked from C4 to C12, For some reason, the cod doesn't change any of the formatting when I change the drop dow value. Any ideas? Thanks -- Matt_2 ----------------------------------------------------------------------- Matt_2K's Profile: http://www.excelforum.com/member.php...nfo&userid=617 View this thread: http://www.excelforum.com/showthread.php?threadid=48102 |
Macro VBA, Drop down IF statement
OK...that's actually different. You want to assign the macro to those drop down's then. Are you using validation for the drop down? or are you using combobox's? Matt_2K Wrote: Thanks for your reply. I have drop downs linked from C4 to C12, For some reason, the code doesn't change any of the formatting when I change the drop down value. Any ideas? Thanks. -- dok112 ------------------------------------------------------------------------ dok112's Profile: http://www.excelforum.com/member.php...o&userid=10581 View this thread: http://www.excelforum.com/showthread...hreadid=481021 |
Macro VBA, Drop down IF statement
I am using combo boxes. -- Matt_2K ------------------------------------------------------------------------ Matt_2K's Profile: http://www.excelforum.com/member.php...fo&userid=6176 View this thread: http://www.excelforum.com/showthread...hreadid=481021 |
Macro VBA, Drop down IF statement
Ok, you want to assign the macro to that combobox then. Depending on type of combobox you are using, when you right click on the combobox, you want to select either "Assign Macro" or "View Code" If you select Assign Macro, then in the Macro pop up box, select the Macro that has the code in it. If you have to select Vew Code, then in the new window, copy and paste the code into it. This one will change the macro name to the combobox name. ie. (View Code) Private Sub ComboBox1_Change() 'enter code here' End Sub -- dok112 ------------------------------------------------------------------------ dok112's Profile: http://www.excelforum.com/member.php...o&userid=10581 View this thread: http://www.excelforum.com/showthread...hreadid=481021 |
Macro VBA, Drop down IF statement
Does that mean I need a macro for each one called DropDown1_Change DropDown2_Change DropDown3_Change etc? -- Matt_2K ------------------------------------------------------------------------ Matt_2K's Profile: http://www.excelforum.com/member.php...fo&userid=6176 View this thread: http://www.excelforum.com/showthread...hreadid=481021 |
Macro VBA, Drop down IF statement
since it's 6 different comboboxes, then unfortunately, yes...you would have to write a command for each box. However, what you can do, is call upon 1 macro in each box. ie. Sub Loop_1() 'all the code' end sub Private Sub dropdown1_change() Loop_1 end sub Private Sub dropdown2_change() Loop_1 end sub etc...so you only will have 1 instance of the macro, but 6 instances of the combobox calling on the macro. -- dok112 ------------------------------------------------------------------------ dok112's Profile: http://www.excelforum.com/member.php...o&userid=10581 View this thread: http://www.excelforum.com/showthread...hreadid=481021 |
Macro VBA, Drop down IF statement
I'm not quite sure if I get what you mean. I've attached my spreadshee in a zip file. I'd be grateful if you'd take a look. I have 8 combo boxes, all assigned to the same macro +------------------------------------------------------------------- |Filename: CoOp Spreadsheet.zip |Download: http://www.excelforum.com/attachment.php?postid=3986 +------------------------------------------------------------------- -- Matt_2 ----------------------------------------------------------------------- Matt_2K's Profile: http://www.excelforum.com/member.php...nfo&userid=617 View this thread: http://www.excelforum.com/showthread.php?threadid=48102 |
Macro VBA, Drop down IF statement
I created a macro called Update, and I assigned each combobox the macr Update. So, now it will switch back and forth for you... +------------------------------------------------------------------- |Filename: CoOp Spreadsheet.zip |Download: http://www.excelforum.com/attachment.php?postid=3987 +------------------------------------------------------------------- -- dok11 ----------------------------------------------------------------------- dok112's Profile: http://www.excelforum.com/member.php...fo&userid=1058 View this thread: http://www.excelforum.com/showthread.php?threadid=48102 |
Macro VBA, Drop down IF statement
Thanks, that works very nicely. Could you explain to me in more depth how it works? I'm interested in how it knows to update all of the values. Thanks again. -- Matt_2K ------------------------------------------------------------------------ Matt_2K's Profile: http://www.excelforum.com/member.php...fo&userid=6176 View this thread: http://www.excelforum.com/showthread...hreadid=481021 |
Macro VBA, Drop down IF statement
Basically, what I did, was take the macro you had with the Loop that updated the values. I changed the name of it to Update, instead of what you had. I then right clicked each of those drop down boxes, and selected the "Assign Macro" option. And then in this dialogue box, I selected the Update macro that we had created. Effectively, what I did was, I assigned each drop down the macro Update...so when ever you make a change w/ the drop down, it will run the macro Update, which in turn is updating those values. I also changed the value of i to 4, instead of 1 in the macro, that way it started out at C4 instead of C1. -- dok112 ------------------------------------------------------------------------ dok112's Profile: http://www.excelforum.com/member.php...o&userid=10581 View this thread: http://www.excelforum.com/showthread...hreadid=481021 |
Macro VBA, Drop down IF statement
Thanks for the explaination, that has cleared it up. When a change is made, it starts the loop which updates them all. -- Matt_2K ------------------------------------------------------------------------ Matt_2K's Profile: http://www.excelforum.com/member.php...fo&userid=6176 View this thread: http://www.excelforum.com/showthread...hreadid=481021 |
Macro VBA, Drop down IF statement
correct. that way you dont need a macro for each row. it just does it all at once... -- dok112 ------------------------------------------------------------------------ dok112's Profile: http://www.excelforum.com/member.php...o&userid=10581 View this thread: http://www.excelforum.com/showthread...hreadid=481021 |
Macro VBA, Drop down IF statement
I inserted 2 columns before it and now it does not work. So all of C needs to be E and all of E needs to be G. I've looked at the macro code, and cant find any reference to these. What do I need to edit to get it to work again? Thanks. -- Matt_2K ------------------------------------------------------------------------ Matt_2K's Profile: http://www.excelforum.com/member.php...fo&userid=6176 View this thread: http://www.excelforum.com/showthread...hreadid=481021 |
Macro VBA, Drop down IF statement
All sorted now, It was column numbers 3 and 5 that i have nw changed. -- Matt_2K ------------------------------------------------------------------------ Matt_2K's Profile: http://www.excelforum.com/member.php...fo&userid=6176 View this thread: http://www.excelforum.com/showthread...hreadid=481021 |
All times are GMT +1. The time now is 10:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com