Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5589
  • Last Modified:

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.
0
1r3o2n8
Asked:
1r3o2n8
  • 9
  • 7
1 Solution
 
sdwalkerCommented:
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

0
 
1r3o2n8Author Commented:
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.
0
 
sdwalkerCommented:
Is FormInput the name of your form?  Can you post your workbook (or at least part of it)?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
1r3o2n8Author Commented:
file is attached.  I want to enter data including date in form and then slide it to spreadsheet with commandbutton
WIRE.xlsm
0
 
sdwalkerCommented:
There's no Sheet1, so you'll need to change the code to say ...
Sheets("Macro1").Range("A1").Value = FormInput.TextBox1.Value
0
 
1r3o2n8Author Commented:
That works.  But the serial number is displayed and the usual conversion methods don't work although it shows above in the formula bar.
0
 
sdwalkerCommented:
Just format the cell as a date and you should be fine.
0
 
1r3o2n8Author Commented:
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?
0
 
sdwalkerCommented:
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?
0
 
1r3o2n8Author Commented:
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.
0
 
1r3o2n8Author Commented:
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.
WIREbb2-090810.xlsm
0
 
sdwalkerCommented:
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.
0
 
1r3o2n8Author Commented:
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
0
 
1r3o2n8Author Commented:
No solution yet but I want to award points
0
 
sdwalkerCommented:
I hate that I can't help further this week as I'm out of town. I'll try to look at it again when I get back.
0
 
1r3o2n8Author Commented:
solver didn't answer last for over 4 days, but initial questions wer OK.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now