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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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