?
Solved

Using calendar in  vba to insert date in text box.

Posted on 2010-09-07
16
Medium Priority
?
5,275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 375 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

800 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