User Forms - Format problems

[AlanAnderson]

I am using Excel 2003. I’ve created a user form to input data into a spreadsheet. My problem is that I can’t “format” comboboxes, text boxes or list boxes on the form<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
For example:<o></o>

• I have text <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:address w:st="on"><st1:Street w:st="on">box</st1:Street> 3</st1:address> which is obtained by adding text <st1:address w:st="on"><st1:Street w:st="on">boxes</st1:Street> 1</st1:address> and 2. Unfortunately the text boxes are outputting numbers in “text” form so my program gives me data mismatch errors.<o></o>
• I have a combobox which accesses a list of dates. Works until I select a date – at that point it displays the date as a number<o></o>

<o> </o>
In short, what code is needed to format a text box as a long number and what code is needed to format a combobox or list box as a date (dd/mm/yy)<o></o>
<o> </o>
Thanks<o></o>

 

[MickG]

Try:-

Private Sub ComboBox1_Change()
   ComboBox1 = Format(ComboBox1, "dd/mm/yyyy")
End Sub

Private Sub CommandButton1_Click()
    TextBox3 = Val(TextBox2) + Val(TextBox1)
End Sub

Mick

 

[AlanAnderson]

Hi Mick,

You are great. I've tried the numbers - works like a dream. Will test the dates a little later.
A related question:

TbStd * 17% = TbTax
tbStd+tbZero+tbExemp +tbTax = Total
What is the best way to have tbTax and tbTotal change whenever any of the other fields change?
Thanks again for your quick and capable help.
Alan

 

[AlanAnderson]

Hi,

I've tried the date format as suggested by Mick:
Combobox1=format(ComboBox1,"dd,mm,yy")

and get error message "Compile error:
Wrong number of arguments or invalid property assignment"

What am I doing wrong?

PS I'd stress I am using Excel2003 on XP platform.

Thanks,

Alan

 

[RoryA]

I'd guess that your combobox is actually bound to your worksheet? If you simply fill it using the list property (and don't use the RowSource or ControlSource at all) then you won't have the problem.

 

[diddi]

the complie error isnt coming from your quoted code. please post entire code for the userform
use the code []s to get the indents posting correctly

 

[AlanAnderson]

Hi Again,
The exact code I am using is as follows

Private sub ComboBox1_Change()
ComboBox1=Format(ComboBox1, "dd/mm/yy")
End sub

Regards,

Alan

 

[diddi]

this code will not work at all as it is. what are you trying to load into the combobox? where are the values coming from. there is more to a combobox control than you think. each element has to be attached to a particular value. as i said post ALL the code if you want us to help you

 

[MickG]

Hi, This is a work around if your Combobox is filled by a "Listfillrange/Rowsource" (Change ref as required), and your List has Text,Numbers & dates.
The code checks the ListFillrange for Dates/Data in relation to your selection and then formats them accordingly.

Private Sub ComboBox1_Change()
With ComboBox1
    If IsDate(Range(.ListFillRange)(.ListIndex + 1)) Then
        ComboBox1 = Format(ComboBox1, "dd/mm/yyyy")
    End If
End With
End Sub

Mick