| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
capture only part of inputBox or of cell contents
Help. In inputBox the user enters a date (not usually the current date) for a
month for which he wants to enter data. This is stored in the variable "userMonth" and entered into Range("F1"). I then use contents of F1 in a Find method to select a cell (labled mmm) into which to paste the entered data. My code checks inputBox entry for a valid date. If entry is valid (say, mmm,yy), here's the rub: I need to use only the mmm part in the Find method. Is it possible to capture only the mmm part of the entry or alternatively to retrieve only the mmm part from F1? Format (Date,"mmm") doesn't work here because the date may not be the current date. I know that I could have the user enter only the mmm in the inputBox but then I can't check that the entry is a valid date (at least I don't think I can). |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
capture only part of inputBox or of cell contents
Give this a whirl...
MsgBox Format(CDate(InputBox("Enter a Date:")), "mmm") -- HTH... Jim Thomlinson "JCIrish" wrote: Help. In inputBox the user enters a date (not usually the current date) for a month for which he wants to enter data. This is stored in the variable "userMonth" and entered into Range("F1"). I then use contents of F1 in a Find method to select a cell (labled mmm) into which to paste the entered data. My code checks inputBox entry for a valid date. If entry is valid (say, mmm,yy), here's the rub: I need to use only the mmm part in the Find method. Is it possible to capture only the mmm part of the entry or alternatively to retrieve only the mmm part from F1? Format (Date,"mmm") doesn't work here because the date may not be the current date. I know that I could have the user enter only the mmm in the inputBox but then I can't check that the entry is a valid date (at least I don't think I can). |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
capture only part of inputBox or of cell contents
Jim,
thanks for the response. I couldn't get that to work. I still end up with an input in the form of mmm,yy in Cell F1 when I'm looking for just mmm "Jim Thomlinson" wrote: Give this a whirl... MsgBox Format(CDate(InputBox("Enter a Date:")), "mmm") -- HTH... Jim Thomlinson "JCIrish" wrote: Help. In inputBox the user enters a date (not usually the current date) for a month for which he wants to enter data. This is stored in the variable "userMonth" and entered into Range("F1"). I then use contents of F1 in a Find method to select a cell (labled mmm) into which to paste the entered data. My code checks inputBox entry for a valid date. If entry is valid (say, mmm,yy), here's the rub: I need to use only the mmm part in the Find method. Is it possible to capture only the mmm part of the entry or alternatively to retrieve only the mmm part from F1? Format (Date,"mmm") doesn't work here because the date may not be the current date. I know that I could have the user enter only the mmm in the inputBox but then I can't check that the entry is a valid date (at least I don't think I can). |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
capture only part of inputBox or of cell contents
dim dt as Date, s as String
On Error Resume Next dt = CDate(InputBox("Enter a Date:")) if err.Number < 0 then msgbox "Bad Date" exit sub Exit sub On Error goto 0 s = format(dt,"mmm") Range("F1").value = "'" & s or Dim s as String s = InputBox("Enter a 3 letter month name abbreviation") if len(s) < 3 then Msgbox "Bad entry" exit sub end if if not isdate(DateValue( s & " 1, 2006")) then Msgbox "Bad entry" exit sub end if Range("F1").Value = "'" & s -- Regards, Tom Ogilvy "JCIrish" wrote: Jim, thanks for the response. I couldn't get that to work. I still end up with an input in the form of mmm,yy in Cell F1 when I'm looking for just mmm "Jim Thomlinson" wrote: Give this a whirl... MsgBox Format(CDate(InputBox("Enter a Date:")), "mmm") -- HTH... Jim Thomlinson "JCIrish" wrote: Help. In inputBox the user enters a date (not usually the current date) for a month for which he wants to enter data. This is stored in the variable "userMonth" and entered into Range("F1"). I then use contents of F1 in a Find method to select a cell (labled mmm) into which to paste the entered data. My code checks inputBox entry for a valid date. If entry is valid (say, mmm,yy), here's the rub: I need to use only the mmm part in the Find method. Is it possible to capture only the mmm part of the entry or alternatively to retrieve only the mmm part from F1? Format (Date,"mmm") doesn't work here because the date may not be the current date. I know that I could have the user enter only the mmm in the inputBox but then I can't check that the entry is a valid date (at least I don't think I can). |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
capture only part of inputBox or of cell contents
Tom,
Works like a charm, and your help taught me some code that I had no clue how to use. Many thanks, JCIrish "Tom Ogilvy" wrote: dim dt as Date, s as String On Error Resume Next dt = CDate(InputBox("Enter a Date:")) if err.Number < 0 then msgbox "Bad Date" exit sub Exit sub On Error goto 0 s = format(dt,"mmm") Range("F1").value = "'" & s or Dim s as String s = InputBox("Enter a 3 letter month name abbreviation") if len(s) < 3 then Msgbox "Bad entry" exit sub end if if not isdate(DateValue( s & " 1, 2006")) then Msgbox "Bad entry" exit sub end if Range("F1").Value = "'" & s -- Regards, Tom Ogilvy "JCIrish" wrote: Jim, thanks for the response. I couldn't get that to work. I still end up with an input in the form of mmm,yy in Cell F1 when I'm looking for just mmm "Jim Thomlinson" wrote: Give this a whirl... MsgBox Format(CDate(InputBox("Enter a Date:")), "mmm") -- HTH... Jim Thomlinson "JCIrish" wrote: Help. In inputBox the user enters a date (not usually the current date) for a month for which he wants to enter data. This is stored in the variable "userMonth" and entered into Range("F1"). I then use contents of F1 in a Find method to select a cell (labled mmm) into which to paste the entered data. My code checks inputBox entry for a valid date. If entry is valid (say, mmm,yy), here's the rub: I need to use only the mmm part in the Find method. Is it possible to capture only the mmm part of the entry or alternatively to retrieve only the mmm part from F1? Format (Date,"mmm") doesn't work here because the date may not be the current date. I know that I could have the user enter only the mmm in the inputBox but then I can't check that the entry is a valid date (at least I don't think I can). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Looking Up Part Cell Contents | Excel Discussion (Misc queries) | |||
| #N/A if cell contents not part of an array | Excel Discussion (Misc queries) | |||
| InputBox to capture user selected sheet names? | Excel Programming | |||
| Can I use cell contents as part of a formula? | Excel Worksheet Functions | |||
| Capture changing cell contents from DDE links | Excel Programming | |||
Linear Mode
