Solved

Using calendar in  vba to insert date in text box.

Posted on 2010-09-07
16
4,891 Views
Last Modified: 2012-06-27
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
Comment
Question by:1r3o2n8
  • 9
  • 7
16 Comments
 
LVL 12

Expert Comment

by:sdwalker
ID: 33620585
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
 

Author Comment

by:1r3o2n8
ID: 33621656
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
 
LVL 12

Expert Comment

by:sdwalker
ID: 33621694
Is FormInput the name of your form?  Can you post your workbook (or at least part of it)?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:1r3o2n8
ID: 33621962
file is attached.  I want to enter data including date in form and then slide it to spreadsheet with commandbutton
WIRE.xlsm
0
 
LVL 12

Expert Comment

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

Author Comment

by:1r3o2n8
ID: 33622443
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
 
LVL 12

Expert Comment

by:sdwalker
ID: 33622996
Just format the cell as a date and you should be fine.
0
 

Author Comment

by:1r3o2n8
ID: 33630494
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
 
LVL 12

Expert Comment

by:sdwalker
ID: 33635268
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
 

Author Comment

by:1r3o2n8
ID: 33640689
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
 

Author Comment

by:1r3o2n8
ID: 33640729
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
 
LVL 12

Expert Comment

by:sdwalker
ID: 33641033
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
 

Author Comment

by:1r3o2n8
ID: 33648178
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
 

Author Comment

by:1r3o2n8
ID: 33665935
No solution yet but I want to award points
0
 
LVL 12

Accepted Solution

by:
sdwalker earned 125 total points
ID: 33666281
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
 

Author Closing Comment

by:1r3o2n8
ID: 33666385
solver didn't answer last for over 4 days, but initial questions wer OK.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question