User input box?

[StargateFanFromWork]

I modified some code kindly given to me this week, trying
to see what I could come up with. User input is
completely new to me so I'm shaky. I've studied the
syntax in the VB help for inputbox but I'm stumped and
code examples for inputbox that I've pulled up with google
haven't yielded anything I can work with. This is quite a
bit more straightforward that some of the code I've seen
<lol>.

Here is the muddle I've made of the given code <g>:

Sub Test()

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter overtime start date
(i.e., 01 for January, 02 for February, etc.):", _
Title:="Overtime Start Date", _
Default:=Format(Date, "mmm"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("AB2").Value = CDate(vResponse)

End Sub

Actually, just changed a couple of things. What I do like
about the above is that it shows "May" as the default
which the user can overwrite. I initially thought that
there might have to be two requests for info for the month
and then the date, but ideally, it would be even better if
the user could input just today's date, say 12/05/2005,
and that Excel would take the "05" for May and put "May"
as text in cell AB2 and then that it would take the date
of "12" and just dumps a "12" in B16. That would be the
ideal, but I'll go with best syntax that is still
efficient. The reason I'd like to automate this for the
employees is due to the fact that the sheet is extremely
cumbersome to work with as it is, and it's quite large.
This way, the users will have a starting point and the
vital month and overtime start date will already be input,
and then the employee can continue from there.

Thanks much. D

 

[Dave Peterson]

I think...

Option Explicit

Sub Test()

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter overtime start date " & _
"(i.e., 01 for January, 02 for February, etc.):", _
Title:="Overtime Start Date", _
Default:=Format(Date, "mmm dd, yyyy"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("AB2").Value = Format(CDate(vResponse), "mmm")
Range("b16").Value = Day(CDate(vResponse))

End Sub

I'd be careful with dates formatted as 12/05/2005. When I see that, I see Dec
5, 2005.

If you're unambiguous (mmm dd, yyyy), it might make it more difficult to screw
up.

(Don't forget to adjust the prompt, too.)

 

[StargateFan]

That's perfect! I didn't realize, the prompt can be _so_ flexible.
You can type the dates in pretty much any way, yyyy mm dd or dd mm
yyyy, short or long and they seem to come out just fine. In my
modification, I'd put the month as a 3-character "mmm" figure because
that's all I knew how to do for a word value. Just now, modified your
code below to get the full date by putting an extra "m" in the 2nd
line from the bottom. So instead of

Range("AB2").Value = Format(CDate(vResponse), "mmm")

I put in

Range("AB2").Value = Format(CDate(vResponse), "mmmm")

Works perfectly!

Thanks for pointing out re order of the dates; that brings up a very
interesting point. At home, my default Excel installation is one way,
whereas at work it's another. I run into interesting things like this
since I do contract work - the variations between installations gets
pretty extraordinary! <g>. At the office 05/12/2005 is today's date
of May 12th, yet at home my May 12th is 12/05/2005! Go figure! <g>

Thanks so much. I'll implement this macro into the overtime
spreadsheet. People will find it so much easier to work with. I
added data validation fields so that all the major information cells
have pulldown boxes with the info that's needed. Along with this
macro and locking all but the cells the users actually need to input
into, they're going to find the process of filling out their forms so
much easier! D

 

[Dave Peterson]

If you want to be consistent between pcs, you may want to look at your regional
settings (under control panel) to make sure the short date formats are the same.

But I would think that having your pc be the same as your co-workers would be
more important.

 

[StargateFan]

If you want to be consistent between pcs, you may want to look at your regional
settings (under control panel) to make sure the short date formats are the same.

But I would think that having your pc be the same as your co-workers would be
more important.

Yes, thanks. That's why I've never changed settings. I just adjust
to each place. So far, the files I bring from home work at the office
and I've made the adjustment in soem of the cell hint prompts that
instead of entering the date a certain way, to just use ^+;. Though
it hasn't seemed to cause problems as people don't pay much attention

 

[StargateFan]

I think...

Option Explicit

Sub Test()

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter overtime start date " & _
"(i.e., 01 for January, 02 for February, etc.):", _
Title:="Overtime Start Date", _
Default:=Format(Date, "mmm dd, yyyy"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("AB2").Value = Format(CDate(vResponse), "mmm")
Range("b16").Value = Day(CDate(vResponse))

End Sub

[snip]

Thanks to these ngs and constant work over the past year, I've really
come a long way. This last week I've made significant strides. I've
really absorbed more than I thought. I've finally been able to make
_working_ modifications to code and have managed to finally work with
code pieces I've found on the net! Pretty neat stuff, all this. One
last thing, I'm thrilled and proud that I figured out how to get the
OK and Cancel buttons into a couple of my inputboxes now by myself,
and then to get them to work!! <g> That took some doing. I know that
type of thing must be old-hat to many, but it's nice to be making
forays into fishing for myself rather than being given the fish <vbg>!

But <sigh>, still run into brick walls. I tried adding an icon to the
above code. Was hoping you or anyone could point me in the right
direction. I've tried putting the vbExclamation in various parts
above where it seemed logical that it might work there, but haven't
gotten it to work. I don't even know if the above code will support
an icon the way inputbox and msgbox codes I've seen can, though don't
see why not; but hoping someone here can help.

Thanks bunches! D

 

[Dave Peterson]

From the Help for msgbox:
MsgBox(prompt[, buttons] [, title] [, helpfile, context])

From the Help for Inputbox:
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

The Msgbox has a buttons parm where you can specify what you want. Inputbox
doesn't have this.

If you really want a special icon, you could design your own userform.

I think...

Option Explicit

Sub Test()

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter overtime start date " & _
"(i.e., 01 for January, 02 for February, etc.):", _
Title:="Overtime Start Date", _
Default:=Format(Date, "mmm dd, yyyy"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("AB2").Value = Format(CDate(vResponse), "mmm")
Range("b16").Value = Day(CDate(vResponse))

End Sub

[snip]

Thanks to these ngs and constant work over the past year, I've really
come a long way. This last week I've made significant strides. I've
really absorbed more than I thought. I've finally been able to make
_working_ modifications to code and have managed to finally work with
code pieces I've found on the net! Pretty neat stuff, all this. One
last thing, I'm thrilled and proud that I figured out how to get the
OK and Cancel buttons into a couple of my inputboxes now by myself,
and then to get them to work!! <g> That took some doing. I know that
type of thing must be old-hat to many, but it's nice to be making
forays into fishing for myself rather than being given the fish <vbg>!

But <sigh>, still run into brick walls. I tried adding an icon to the
above code. Was hoping you or anyone could point me in the right
direction. I've tried putting the vbExclamation in various parts
above where it seemed logical that it might work there, but haven't
gotten it to work. I don't even know if the above code will support
an icon the way inputbox and msgbox codes I've seen can, though don't
see why not; but hoping someone here can help.

Thanks bunches! D

 

[StargateFan]

From the Help for msgbox:
MsgBox(prompt[, buttons] [, title] [, helpfile, context])

From the Help for Inputbox:
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

The Msgbox has a buttons parm where you can specify what you want. Inputbox
doesn't have this.

(Parm?) <g>, thanks for the info. The above is still Chinese to me.
Of course I understand the words, but not in the context above re
programming language except vaguely. It's a whole new language, this
vba. But good to know final answer, that it can't be done

If you really want a special icon, you could design your own userform.

Geez, are you kidding?? I had enough trouble with this. Boy, you
guys are way beyond me that you can't remember what it's like to be a
rank newbie, eh? <g> No, I'll stick with what I've got. I'll have to
live with this as it is. It's not perfect, but it works. It was
looking pulling teeth to get this far so I'm not going to go looking
for any more trouble.

Tx. D

I think...

Option Explicit

Sub Test()

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter overtime start date " & _
"(i.e., 01 for January, 02 for February, etc.):", _
Title:="Overtime Start Date", _
Default:=Format(Date, "mmm dd, yyyy"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("AB2").Value = Format(CDate(vResponse), "mmm")
Range("b16").Value = Day(CDate(vResponse))

End Sub

[snip]

Thanks to these ngs and constant work over the past year, I've really
come a long way. This last week I've made significant strides. I've
really absorbed more than I thought. I've finally been able to make
_working_ modifications to code and have managed to finally work with
code pieces I've found on the net! Pretty neat stuff, all this. One
last thing, I'm thrilled and proud that I figured out how to get the
OK and Cancel buttons into a couple of my inputboxes now by myself,
and then to get them to work!! <g> That took some doing. I know that
type of thing must be old-hat to many, but it's nice to be making
forays into fishing for myself rather than being given the fish <vbg>!

But <sigh>, still run into brick walls. I tried adding an icon to the
above code. Was hoping you or anyone could point me in the right
direction. I've tried putting the vbExclamation in various parts
above where it seemed logical that it might work there, but haven't
gotten it to work. I don't even know if the above code will support
an icon the way inputbox and msgbox codes I've seen can, though don't
see why not; but hoping someone here can help.

Thanks bunches! D

 

[David McRitchie]

Hi stargate,
Now that you mention it those definitions are a bit off, since
they don't show that an omitted parameter followed by additional
parameters must retain a comma as a placeholder.

Suggest you look at the Inputbox Function in the VBE help
and the example which are strictly positional arguments.

Afterwards you can look at the Inputbox Method in the VBE help
noting that each positional argument has a name.

InputBox, MsgBox and TextBox
http://www.mvps.org/dmcritchie/excel/inputbox.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

From the Help for msgbox:
MsgBox(prompt[, buttons] [, title] [, helpfile, context])

From the Help for Inputbox:
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

The Msgbox has a buttons parm where you can specify what you want. Inputbox
doesn't have this.

(Parm?) <g>, thanks for the info. The above is still Chinese to me.
Of course I understand the words, but not in the context above re
programming language except vaguely. It's a whole new language, this
vba. But good to know final answer, that it can't be done

If you really want a special icon, you could design your own userform.

Geez, are you kidding?? I had enough trouble with this. Boy, you
guys are way beyond me that you can't remember what it's like to be a
rank newbie, eh? <g> No, I'll stick with what I've got. I'll have to
live with this as it is. It's not perfect, but it works. It was
looking pulling teeth to get this far so I'm not going to go looking
for any more trouble.

Tx. D

On Thu, 12 May 2005 18:18:47 -0500, Dave Peterson

I think...

Option Explicit

Sub Test()

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter overtime start date " & _
"(i.e., 01 for January, 02 for February, etc.):", _
Title:="Overtime Start Date", _
Default:=Format(Date, "mmm dd, yyyy"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("AB2").Value = Format(CDate(vResponse), "mmm")
Range("b16").Value = Day(CDate(vResponse))

End Sub

[snip]

Thanks to these ngs and constant work over the past year, I've really
come a long way. This last week I've made significant strides. I've
really absorbed more than I thought. I've finally been able to make
_working_ modifications to code and have managed to finally work with
code pieces I've found on the net! Pretty neat stuff, all this. One
last thing, I'm thrilled and proud that I figured out how to get the
OK and Cancel buttons into a couple of my inputboxes now by myself,
and then to get them to work!! <g> That took some doing. I know that
type of thing must be old-hat to many, but it's nice to be making
forays into fishing for myself rather than being given the fish <vbg>!

But <sigh>, still run into brick walls. I tried adding an icon to the
above code. Was hoping you or anyone could point me in the right
direction. I've tried putting the vbExclamation in various parts
above where it seemed logical that it might work there, but haven't
gotten it to work. I don't even know if the above code will support
an icon the way inputbox and msgbox codes I've seen can, though don't
see why not; but hoping someone here can help.

Thanks bunches! D