Solved

Using calendar in  vba to insert date in text box.

Posted on 2010-09-07
16
4,811 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

785 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