[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Using calendar in  vba to insert date in text box.

Posted on 2010-09-07
16
Medium Priority
?
5,413 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

649 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