TextBox Date Format - VBA?

Question

Hello ..

i want to make Date Format for TextBox (TextBox1) with the Normal Code at Excel VBA

what i did :

'Dim dDate As Date
dDate = DateSerial(Year(Date), Month(Date), Day(Date))
TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy")
dDate = TextBox1.Value
----------------------------------------------------------------------------------------------------------------------
and its Work Good with format .. but when adding Code for Results Not Working :



Me.TextBox1 = Me.ComboBox1.Column(12)

'Dim dDate As Date
dDate = DateSerial(Year(Date), Month(Date), Day(Date))
TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy")
dDate = TextBox1.Value

-----------------------------------------------------------------------------------------------------
Please Help ..
==================================================

Answer 1

Assignment to the Text Box doesn't work because you haven't specified enough arguments.



MS Access is a far superior tool, so I rarely use Excel for anything at all ─ but they both use the same VBA. This is the way it would be done in Access.



If 12 is the ROW of the selected item, the COLUMN is missing:

TextBox1 = ComboBox1.Column(col, ComboBox1.ListIndex)



If 12 is the COLUMN of the selected item, the ROW is missing:

TextBox1 = ComboBox1.Column(col, ComboBox1.ListIndex)



You CAN use the literal value of 12 (for the row), but ListIndex is better.



OR



If the first column of the Combo Box is the bound column:

TextBox1 = ComboBox1

Answer 2

Date Format Vba

Answer 3

Ok, I think I get what you are trying to do. I've done stuff similar to that a number of times before myself. From what I understand if I get this right is you want to have combo1 filled when the form initializes. Then then a selection is made from combo1, then the list for combo2 is populated. Then when a selection from combo2 is made, then the list for combo3 is populated. Here's some suggestions I'd make: 1. Populate your list slightly differently. Use the Intersect() method to get all of the names. You may be missing a larger portion of the names if you do it the way you are doing. I'd do it something like this: Dim rng1 As Range Dim rng2 As Range Set rng1 = Intersect(ActiveSheet.UsedRange, Range("A:A")) For Each rng2 In rng1 If rng2.Value <> "" Then cmb1.AddItem rng2.Value End If Next rng2 With your code, when it gets to the first blank cell it will quit adding names to the list. This makes sure the macro grabs every filled cell in that column. You might also put another if in there to avoid adding the header cell. Like you could put something like: If rng2.Value <> "" And _ rng2.Value <> Cells(ActiveSheet.UsedRange.Row, 1) Then This skips the first row in the Used range. The header row might not always be in Row 1 if the data starts on say row 3. 2. I recommend disabling combos 2 & 3 until the user makes the appropriate selections in Combos 1 and 2. Use the Change event for the combo box to trigger populating the next combo box and enabling it. You don't want combos 2 and 3 enabled until the appropriate time or the user could mess things up. Here's what it should somewhat look like: Private Sub cmb1_Change() cmb2.Clear cmb3.Clear If cmb1.ListIndex = -1 Then MsgBox "Please pick a value from the list.", vbOKOnly + vbExclamation, "Entry Error" cmb2.Enabled = False cmb3.Enabled = False Exit Sub Else cmb2.Enabled = True cmb3.Enabled = False 'Code to populate combo2 here End If End Sub Private Sub cmb2_Change() cmb3.Clear If cmb2.ListIndex = -1 Then MsgBox "Please pick a value from the list.", vbOKOnly + vbExclamation, "Entry Error" cmb3.Enabled = False Exit Sub Else cmb3.Enabled = False 'Code to populate combo3 here End If End Sub Here's basically what they do. First you want to clear the current lists of the next combo boxes. If the user makes a change to combo1 you want to clear the lists for combos 2 and 3. If the user makes a change to combo 2 then clear the list for combo 3. This makes sure the list doesn't have multiple lists added to it. And it avoids potential errors. Secondly, you want to do the ListIndex check to make sure the value isn't -1. If it is -1 then that means the user typed in a value that isn't in the List for that combo box. You want to allow the user to type in values, but you also want to make sure that the value is a value in the list. This check does that. Then you want to enable and disable combo boxes. If the user makes a change to combo1 you want to make sure combo2 is enabled as long as the ListIndex does not equal -1. And you want to make sure that combo3 is not enabled. You only want the very next combo enabled if the user makes the right selection. You have to try thinking of every possible thing the user might do. I might make selections for all three then I go back and decide...WAIT!!! I need to change combo1. So I need Combo1 enabled and when I make a change to Combo 1, then I need to disable and clear comb3. I try making my VBA programs as user friendly as I can and I try accounting for every possible "monkey wrench in the clockworks" change they might make so I avoid some error happening. As far as what you want to do with the text boxes, that is not the best idea. It can be done but you are potentially evaluating a long string of text. I'd rather force the user to enter dates in an inputbox or into a cell where you can control what they enter much more easily and format it more easily. Think about it. What if they wrote this in the textbox: "On 12/12/2009 I headed to work." How do you change that? Or what if they put in... "On December 12, 2009 I headed to work." Then in European style they put the day first. And you are evaluating strings which are not easy to evaluate. And there are tons of styles the text could be entered in. Limit what they can put in. Or even use something like a DatePicker control.