Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Woes
Greetings again,
A new problem popped up when I tried to change my date format in a TextBox from mmm dd, yyyy to mmm dd, yyyy ddd. The old format allowed the "date1" to work but the new formant stops the code from going past the date1 segment of code. The format itself works as far as displaying the date in that TextBox, but not in the date1 code segment. Here is the code: date1 = TB1.Value If date1 = "9/15/97" Then TB11.Value = ".0825" If date1 < "9/15/97" Then TB11.Value = ".08" I know it has something to do with formatting, but I don't know how. Any help would be appreciated. TIA -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Woes
Hi
you first have to convert your textbox value to a real date. Excel probably does not recognise it as a valid date. Also I'd suggest to use DateSerial for the comparison instead of a string value -- Regards Frank Kabel Frankfurt, Germany "Minitman" schrieb im Newsbeitrag ... Greetings again, A new problem popped up when I tried to change my date format in a TextBox from mmm dd, yyyy to mmm dd, yyyy ddd. The old format allowed the "date1" to work but the new formant stops the code from going past the date1 segment of code. The format itself works as far as displaying the date in that TextBox, but not in the date1 code segment. Here is the code: date1 = TB1.Value If date1 = "9/15/97" Then TB11.Value = ".0825" If date1 < "9/15/97" Then TB11.Value = ".08" I know it has something to do with formatting, but I don't know how. Any help would be appreciated. TIA -Minitman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Woes
Hey Frank,
How do I do that? -Minitman On Wed, 15 Dec 2004 21:50:37 +0100, "Frank Kabel" wrote: Hi you first have to convert your textbox value to a real date. Excel probably does not recognise it as a valid date. Also I'd suggest to use DateSerial for the comparison instead of a string value -- Regards Frank Kabel Frankfurt, Germany "Minitman" schrieb im Newsbeitrag .. . Greetings again, A new problem popped up when I tried to change my date format in a TextBox from mmm dd, yyyy to mmm dd, yyyy ddd. The old format allowed the "date1" to work but the new formant stops the code from going past the date1 segment of code. The format itself works as far as displaying the date in that TextBox, but not in the date1 code segment. Here is the code: date1 = TB1.Value If date1 = "9/15/97" Then TB11.Value = ".0825" If date1 < "9/15/97" Then TB11.Value = ".08" I know it has something to do with formatting, but I don't know how. Any help would be appreciated. TIA -Minitman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Woes
Minitman,
What is putting the text in the textbox? Code, a default value, or the user? The problem here is that VBA does not recognize a date in that format: ?IsDate("Dec 15, 2004 Wed") False So you'll either have to parse out the date or use a different date format that is recognizable by VBA. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Minitman wrote: Greetings again, A new problem popped up when I tried to change my date format in a TextBox from mmm dd, yyyy to mmm dd, yyyy ddd. The old format allowed the "date1" to work but the new formant stops the code from going past the date1 segment of code. The format itself works as far as displaying the date in that TextBox, but not in the date1 code segment. Here is the code: date1 = TB1.Value If date1 = "9/15/97" Then TB11.Value = ".0825" If date1 < "9/15/97" Then TB11.Value = ".08" I know it has something to do with formatting, but I don't know how. Any help would be appreciated. TIA -Minitman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Woes
Hey Jake,
The date is imputed from two sources. The first is on the spreadsheet as a starting point. The second is on a UserForm. I formatted the cell with a custom format (mmm dd, yyyy ddd) so that I could see what day of the week I was working on. The old format was (mmm dd,yyyy) and the code worked with it. The UserForm is designed to cycle after each Save event (you hit Save and the entries are saved and the UserForm is unloaded then reloaded). The first thing that pops up are three buttons (everything else is hidden), you choose one and the buttons hide and the date TextBox pops up. You either change the date or hit enter to continue. This date gets it's default value from the date on the sheet each time I hit the Save button. When you exit this date, it then changes the value on the spreadsheet cell to what ever is in the TextBox. This is working fine, even with my custom format. The problem occurs when I try to use the TextBox date to determine if a variable should equal one constant or another, there being a cutoff date involved (9/15/97 or in MS, 35690). What I am looking for is a way to convert/varse or somehow make the TextBox entry readable as a date. How can I do this? TIA -Minitman On Wed, 15 Dec 2004 16:50:18 -0700, "Jake Marx" wrote: Minitman, What is putting the text in the textbox? Code, a default value, or the user? The problem here is that VBA does not recognize a date in that format: ?IsDate("Dec 15, 2004 Wed") False So you'll either have to parse out the date or use a different date format that is recognizable by VBA. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Woes
Hi Minitman,
Something like this should do the trick: Public Function gsConvertDate(rsOldDate As String) As String Dim nPos As Integer nPos = InStrRev(rsOldDate, " ") If nPos Then gsConvertDate = Left$(rsOldDate, nPos - 1) Else gsConvertDate = rsOldDate End If End Function -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Jake Marx wrote: Minitman, What is putting the text in the textbox? Code, a default value, or the user? The problem here is that VBA does not recognize a date in that format: ?IsDate("Dec 15, 2004 Wed") False So you'll either have to parse out the date or use a different date format that is recognizable by VBA. Minitman wrote: Greetings again, A new problem popped up when I tried to change my date format in a TextBox from mmm dd, yyyy to mmm dd, yyyy ddd. The old format allowed the "date1" to work but the new formant stops the code from going past the date1 segment of code. The format itself works as far as displaying the date in that TextBox, but not in the date1 code segment. Here is the code: date1 = TB1.Value If date1 = "9/15/97" Then TB11.Value = ".0825" If date1 < "9/15/97" Then TB11.Value = ".08" I know it has something to do with formatting, but I don't know how. Any help would be appreciated. TIA -Minitman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Woes
Hi,
I often use a calendar to update dates. It is fairly easy with the built in one, and it allows you to see the day of the week. In the VBA editor add a user form, right click on the toolbox, click the additional controls option, and you will see an option called calendar control. When you select that and click O.K. it will add a calander control. This is an example of the code I have for my calander form: '################################################# ####### Public Sub Calendar1_Click() 'Change target cells as necessary Range(TargetCellDate).Value = Calendar1.Value Range(TargetCellDate).Offset(rowoffset:=1, columnoffset:=0).Select ActiveSheet.Calculate Unload Me End Sub '################################################# ####### Public Sub UserForm_Initialize() ' Set starting conditions. With frmCalendar ..Calendar1.Value = Range(TargetCellDate) ..Calendar1.SetFocus End With End Sub '################################################# ####### Here is the code I have in a worksheet to call the calendar function: '################################################# ####### Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) Dim C% Cancel = True TargetCellDate = Target.Address(False, False) Select Case TargetCellDate Case "B2" C = 2 Case "B3" C = 3 Case "B4" C = 4 Case "B5" C = 5 Case Else C = 0 End Select If C = 1 Then frmCalendar.Show 'MsgBox "Cell " & Target.Address(False, False) & " Worked." End If End Sub '################################################# ####### This isn't exactly what you are doing, but it should get you in the ball park. I hope I didn't open a can of worms. It works nicely once you get it setup. HTH--Lonnie M. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formatting Woes
In my example, TargetCellDate was a global variable.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rowliner woes | Excel Discussion (Misc queries) | |||
Date Cell/ Format woes! | Excel Discussion (Misc queries) | |||
HELP: Dir() time-out woes :( ..... | New Users to Excel | |||
More Conditional Formatting Woes | Excel Discussion (Misc queries) | |||
COUNTIF woes | Excel Worksheet Functions |