asked on

Using calendar in vba to insert date in text box.

Private Sub Calendar1_Click()

ActiveCell = TextBox1 'Calendar1.Value -this wqs tried but didn't work.
ActiveCell.NumberFormat = "mm/dd/yy"

End Sub
Private Sub userform_activate()

Me.Calendar1.Value = Date

End Sub
Sub showit()

FormInput.Show

End Sub

I want to insert a date selected from vba calendar into textbox1 located on a userform. which will later be inserted into the spreadsheet with a submit command on userform. The above code doesn't work. Please help. Thanx.

sdwalker

I think you'll need to use something more like this ...
Good luck,
sdwalker

Private Sub Calendar1_Click()

' this code gets it to the userform
TextBox1.Text = Format(Calendar1.Value, "mm/dd/yyyy")

' this code gets it to the spreadsheet
sheets("Sheet1").Range("A1").value = FormInput.TextBox1.Value ' or it could be FormInput.TextBox1.Text

End Sub

Private Sub userform_activate()

Calendar1.Value = Date

End Sub

Sub showit()

FormInput.Show

End Sub

Open in new window

1r3o2n8

ASKER

Private Sub CommandButton1_Click()

'Dim r As Range
'Set r = Range("e30").End(xlUp) 'a65536
'Set r = r.Offset(1, 0)
'r.Value = TextBox1.Value

Sheets("Sheet1").Range("A1").Value = FormInput.TextBox1.Value

End Sub

Your line above errors out-runtime error 9, subscript out of range. Textbox1 and commanbutto1 appear correct.

sdwalker

Is FormInput the name of your form? Can you post your workbook (or at least part of it)?

1r3o2n8

ASKER

file is attached. I want to enter data including date in form and then slide it to spreadsheet with commandbutton
WIRE.xlsm

sdwalker

There's no Sheet1, so you'll need to change the code to say ...
Sheets("Macro1").Range("A1").Value = FormInput.TextBox1.Value

1r3o2n8

ASKER

That works. But the serial number is displayed and the usual conversion methods don't work although it shows above in the formula bar.

sdwalker

Just format the cell as a date and you should be fine.

1r3o2n8

ASKER

I am unable to format the date or anything else on the spreadsheet. Also the table keeps disappearing. Do you have any suggestions. Is this file corrupt or is there a problem with a macro?

sdwalker

Is it the same spreadsheet you sent? I was able to right click on the cell and format it as a date with no problem. I just don't know why you would be unable to format a cell unless it's protected. Are you clearing out any data in you're macro?

1r3o2n8

ASKER

TextBox1.Text = Format(Calendar1.Value, "mm/dd/yy")
Does this code or some other insert text into the cell and then format won't work on text? or might text be inserted by another macro? Is a number rather than text necessary in order to format a date? I am going back and slowly redesigning the spreadsheet and trying the formatting frequently to see if and when the format won't work. Unformatable sheet is attached.

1r3o2n8

ASKER

sdwalker

Is the date not showing directly in the form or the spreadsheet? The form field needs to be set up properly if that's where your problem is. The spreadsheet should just require you too format the cell.

Unfortunately, I'm not at a place where I can sew the spreadsheet, so it'll be later before I can look at that.

1r3o2n8

ASKER

Sheets("Macro1").Range("A1").Value = FormInput.TextBox1.Value 'runtime error 9-subscript out of range
Please correct above code. See entire workbook attched. I still sometimes have a problem with formatting. When I try to format, a box comes up with only the font choice and not the number. Why?
WIRE.xlsm

1r3o2n8

ASKER

No solution yet but I want to award points

ASKER CERTIFIED SOLUTION

sdwalker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

1r3o2n8

ASKER

solver didn't answer last for over 4 days, but initial questions wer OK.