MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA code

Posted by Ania on January 24, 2002 1:53 PM

Can somebody tell me why doesn't this work?

Private Sub ComboBox2_Change()
ComboBox2.Value = Format(ComboBox2.Value, "Medium Date")
Range("C12") = ComboBox2.Value
End Sub

Posted by Tom Dickinson on January 24, 2002 3:17 PM

I don't claim to know all, but I have never heard of "Medium date" before. Is this a valid format? If it happens to be a format you created, then it could be having problems with the quotes.

Posted by Juan Pablo G. on January 24, 2002 3:51 PM

I think the issue here can be that you're going in a infinite loop. When you enter the Combobox2_Change() event is because the Value just changed, then, at the beginning, you change the value, which then starts the _Change() event again, and then you change again the value... for ever...

I think what you want to do may be this

Private Sub ComboBox2_Change()
Range("C12") = Format(ComboBox2,"Medium Date")
End Sub

Juan Pablo G.

Posted by Ania on January 25, 2002 6:45 AM

Posted by Ania on January 25, 2002 6:46 AM

I think the problem with my code is that Format is looking for an object... It is not recognizing the ComboBox.Value as a valid object to apply format to...