[SOLVED] Userforms in Excel

StagColumbus · 01-05-2008, 02:02 PM

I have been attempting to automate some data entry into daily logs. While Access would be more ideal, this group of people will only use Excel. So, I have experimented with Userforms for entering data into excel spreadsheets by finding some VBA code and modifying it. My question is this:

How can I get Excel to allow a field in the Userform to have a default date/time value when the Userform is opened?

Previous attempts: Tried to used the control source in the field's property box, which would pull the current time in from the main spreadsheet. However, if the individual needs to put in a different time, then the original field in the spreadsheet is written over with the new value...thus killing any date functions already existing in that spreadsheet cell.

Side note: any good resources for working with Userforms in Excel? My keyword searches have had poor results.

Thanks,
Stag

**Glaswegian** · 01-05-2008, 02:47 PM

Hi and welcome to TSF.

I usually add in today's date when the workbook opens and the form is initialised, but it's just a case of adapting the formatting for time, and lock the field

Code:

With UserForm1.TextBox1
    .Value = Format(Now(), "hh:mm:ss")
    .Locked = True
End With

This assumes you use a Textbox on the form.

Let me know if this helps.

StagColumbus · 01-05-2008, 05:00 PM

IAIN,

Great! That allowed me to fix several similar date/time issues on the different input forms I am creating.

I also appreciate the quick response! I'm in the office this weekend to get this idea right for Monday...now I just might make it! :))

Thanks!
Stag

mseaver · 03-25-2009, 04:15 PM

Quote:

Originally Posted by Glaswegian

Hi and welcome to TSF.

Code:

With UserForm1.TextBox1
    .Value = Format(Now(), "hh:mm:ss")
    .Locked = True
End With

This assumes you use a Textbox on the form.

Let me know if this helps.

This looks very close to what I need to do. If the text box this refers to is named "IssueDate" how would I reference that? I tried "With UserForm1.IssueDate" but it gave me errors.

Also, all of my code executes upon clicking the OK button... how do I set it so this runs when the form is started?

Thanks in advance.

**Glaswegian** · 03-26-2009, 07:13 AM

Hi and welcome.

Is your form named 'UserForm1' or something else? Can you post the code you are using?

mseaver · 03-26-2009, 02:59 PM

Actually, I found a workaround that doesn't require the default value to be set. Thanks, though!

01-05-2008, 02:02 PM	#1 (permalink)
StagColumbus Registered User Join Date: Jan 2008 Posts: 6 OS: windows xp pro	[SOLVED] Userforms in Excel I have been attempting to automate some data entry into daily logs. While Access would be more ideal, this group of people will only use Excel. So, I have experimented with Userforms for entering data into excel spreadsheets by finding some VBA code and modifying it. My question is this: How can I get Excel to allow a field in the Userform to have a default date/time value when the Userform is opened? Previous attempts: Tried to used the control source in the field's property box, which would pull the current time in from the main spreadsheet. However, if the individual needs to put in a different time, then the original field in the spreadsheet is written over with the new value...thus killing any date functions already existing in that spreadsheet cell. Side note: any good resources for working with Userforms in Excel? My keyword searches have had poor results. Thanks, Stag

01-05-2008, 02:47 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,482 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Userforms in Excel Hi and welcome to TSF. I usually add in today's date when the workbook opens and the form is initialised, but it's just a case of adapting the formatting for time, and lock the field Code: With UserForm1.TextBox1 .Value = Format(Now(), "hh:mm:ss") .Locked = True End With This assumes you use a Textbox on the form. Let me know if this helps. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

01-05-2008, 05:00 PM	#3 (permalink)
StagColumbus Registered User Join Date: Jan 2008 Posts: 6 OS: windows xp pro	Re: Userforms in Excel IAIN, Great! That allowed me to fix several similar date/time issues on the different input forms I am creating. I also appreciate the quick response! I'm in the office this weekend to get this idea right for Monday...now I just might make it! :)) Thanks! Stag

03-26-2009, 07:13 AM	#5 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,482 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: [SOLVED] Userforms in Excel Hi and welcome. Is your form named 'UserForm1' or something else? Can you post the code you are using? __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

03-26-2009, 02:59 PM	#6 (permalink)
mseaver Registered User Join Date: Mar 2009 Posts: 2 OS: XP	Re: [SOLVED] Userforms in Excel Actually, I found a workaround that doesn't require the default value to be set. Thanks, though!