Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
I'm doing something in Excel 2003 which I think is fairly basic. I'm
creating a time entry worksheet function which will be used by administrators to enter the daily hours for a group of employees. Instead of entering "8:30", I would like to avoid typing the colon and have the user into "830". I would then like this to be stored and displayed in the cell as "08:30 AM". This requires a simple calculation to convert the "830" to the proper time format, but I can't figure out how to return to the active cell a calculated value which is calculated based on input from the user. I know there has to be a way. Any help would be greatly appreciated. -- CJM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
CJM,
I've tried to think of all the things they could enter that won't work, but I've probably missed some. Paste the following into the worksheet module. It does the conversion for any value entered into Column B. The long If statement checks for, in order, 3 or 4 characters, whether it's an integer, if the hour digits are less than or equal to 24, if the minute digits are less than or equal to 60. I tried to structure it so that you only have the error message in one place, but got a compile error on non-numerics until I put that test first. You have to enter these numbers as military time, e.g. 1430 for 2:30 pm. One weird result is that 12:30 AM must be entered as "24:30". Al in all I think I'd just have them enter the colon, but it is an interesting problem. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("B:B")) Is Nothing Then If IsNumeric(Target) Then If (Len(Target) = 3 Or Len(Target) = 4) And _ Int(Target) = Target And _ Left$(Target, WorksheetFunction.Max(Len(Target) - 2, 1)) <= 24 And _ Right$(Target, 2) <= 59 Then Target = Left$(Target, Len(Target) - 2) & ":" & Right$(Target, 2) If Left$(Target, 2) = "24" Then Target = Replace(Target, "24", "0") End If Target.NumberFormat = "h:mm AM/PM" Else Target.Clear Target.Select MsgBox "that's not a time" End If Else Target.Clear Target.Select MsgBox "that's not a time" End If End If Application.EnableEvents = True End Sub hth, Doug "CJM" wrote in message ... I'm doing something in Excel 2003 which I think is fairly basic. I'm creating a time entry worksheet function which will be used by administrators to enter the daily hours for a group of employees. Instead of entering "8:30", I would like to avoid typing the colon and have the user into "830". I would then like this to be stored and displayed in the cell as "08:30 AM". This requires a simple calculation to convert the "830" to the proper time format, but I can't figure out how to return to the active cell a calculated value which is calculated based on input from the user. I know there has to be a way. Any help would be greatly appreciated. -- CJM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
Doug - Thanks so much for your thorough response. I really appreciate it.
I'm in the process of trying it now and will let you know. The reason for avoiding the colon is that the payroll clerks are quite error prone, and if they have to enter a colon, that requires two entries, the shift and then the colon, hence more chance for error. Many of the clerks are good at using the number pad, but if they have to stop to enter a shift and colon, they take their hands off the number pad and hence further chance of error. I've actually seen it happen. -- CJM "Doug Glancy" wrote: CJM, I've tried to think of all the things they could enter that won't work, but I've probably missed some. Paste the following into the worksheet module. It does the conversion for any value entered into Column B. The long If statement checks for, in order, 3 or 4 characters, whether it's an integer, if the hour digits are less than or equal to 24, if the minute digits are less than or equal to 60. I tried to structure it so that you only have the error message in one place, but got a compile error on non-numerics until I put that test first. You have to enter these numbers as military time, e.g. 1430 for 2:30 pm. One weird result is that 12:30 AM must be entered as "24:30". Al in all I think I'd just have them enter the colon, but it is an interesting problem. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("B:B")) Is Nothing Then If IsNumeric(Target) Then If (Len(Target) = 3 Or Len(Target) = 4) And _ Int(Target) = Target And _ Left$(Target, WorksheetFunction.Max(Len(Target) - 2, 1)) <= 24 And _ Right$(Target, 2) <= 59 Then Target = Left$(Target, Len(Target) - 2) & ":" & Right$(Target, 2) If Left$(Target, 2) = "24" Then Target = Replace(Target, "24", "0") End If Target.NumberFormat = "h:mm AM/PM" Else Target.Clear Target.Select MsgBox "that's not a time" End If Else Target.Clear Target.Select MsgBox "that's not a time" End If End If Application.EnableEvents = True End Sub hth, Doug "CJM" wrote in message ... I'm doing something in Excel 2003 which I think is fairly basic. I'm creating a time entry worksheet function which will be used by administrators to enter the daily hours for a group of employees. Instead of entering "8:30", I would like to avoid typing the colon and have the user into "830". I would then like this to be stored and displayed in the cell as "08:30 AM". This requires a simple calculation to convert the "830" to the proper time format, but I can't figure out how to return to the active cell a calculated value which is calculated based on input from the user. I know there has to be a way. Any help would be greatly appreciated. -- CJM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
It works beautifully. That really helped a lot. Thanks again. I used to
be a really good programmer back in the day, but things have changed so much and I've been out of the loop for a little while. Of course, as fate would have it, I'm thrown back into the frey when my client asks me to "quickly" develop a time entry spreadsheet for them. The only issue I have is that one of the requirements is that the time entry not be in military time -- the "start time" entered by the user must default to am and the "end time" must default to pm. This is a must as apparently the payroll clerks get confused on military time conversions. Now that the code is working in military time (thanks to you), I'm thinking that perhaps the best way to handle the military time issue is to add at the beginning of the code a prompt to the user with a radio button for am or pm. The radio button would default to am for the "start time" column and to pm for the "end time" column. The calculations would then proceed based on the selection. The user would then enter "830" and then hit enter twice or enter once with a cursor move. What do you think and is it possible to add the radio button as I've described? -- CJM "CJM" wrote: Doug - Thanks so much for your thorough response. I really appreciate it. I'm in the process of trying it now and will let you know. The reason for avoiding the colon is that the payroll clerks are quite error prone, and if they have to enter a colon, that requires two entries, the shift and then the colon, hence more chance for error. Many of the clerks are good at using the number pad, but if they have to stop to enter a shift and colon, they take their hands off the number pad and hence further chance of error. I've actually seen it happen. -- CJM "Doug Glancy" wrote: CJM, I've tried to think of all the things they could enter that won't work, but I've probably missed some. Paste the following into the worksheet module. It does the conversion for any value entered into Column B. The long If statement checks for, in order, 3 or 4 characters, whether it's an integer, if the hour digits are less than or equal to 24, if the minute digits are less than or equal to 60. I tried to structure it so that you only have the error message in one place, but got a compile error on non-numerics until I put that test first. You have to enter these numbers as military time, e.g. 1430 for 2:30 pm. One weird result is that 12:30 AM must be entered as "24:30". Al in all I think I'd just have them enter the colon, but it is an interesting problem. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("B:B")) Is Nothing Then If IsNumeric(Target) Then If (Len(Target) = 3 Or Len(Target) = 4) And _ Int(Target) = Target And _ Left$(Target, WorksheetFunction.Max(Len(Target) - 2, 1)) <= 24 And _ Right$(Target, 2) <= 59 Then Target = Left$(Target, Len(Target) - 2) & ":" & Right$(Target, 2) If Left$(Target, 2) = "24" Then Target = Replace(Target, "24", "0") End If Target.NumberFormat = "h:mm AM/PM" Else Target.Clear Target.Select MsgBox "that's not a time" End If Else Target.Clear Target.Select MsgBox "that's not a time" End If End If Application.EnableEvents = True End Sub hth, Doug "CJM" wrote in message ... I'm doing something in Excel 2003 which I think is fairly basic. I'm creating a time entry worksheet function which will be used by administrators to enter the daily hours for a group of employees. Instead of entering "8:30", I would like to avoid typing the colon and have the user into "830". I would then like this to be stored and displayed in the cell as "08:30 AM". This requires a simple calculation to convert the "830" to the proper time format, but I can't figure out how to return to the active cell a calculated value which is calculated based on input from the user. I know there has to be a way. Any help would be greatly appreciated. -- CJM |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
Just thinking out loud, I think another way to solve the military time
problem would be to only execute the worksheet_change if the cell contains no value. See if you follow what I'm thinking and if it makes sense: Cell is blank User enters 830. Cell returns 8:30 AM User really wanted 8:30 PM, so user manually overwrites the AM to PM. Cell does not return an error message but allows the overwrite and stores the correct entry. NOW, this sounds like a lot of steps, but 99% of the time, the "start time" will always be AM and the "end time" pm, so all in all it may be less clicks for the user. Is what I described possible and how would you do it? I'm going to play around and see if I can figure it out. Thanks. -- CJM "CJM" wrote: It works beautifully. That really helped a lot. Thanks again. I used to be a really good programmer back in the day, but things have changed so much and I've been out of the loop for a little while. Of course, as fate would have it, I'm thrown back into the frey when my client asks me to "quickly" develop a time entry spreadsheet for them. The only issue I have is that one of the requirements is that the time entry not be in military time -- the "start time" entered by the user must default to am and the "end time" must default to pm. This is a must as apparently the payroll clerks get confused on military time conversions. Now that the code is working in military time (thanks to you), I'm thinking that perhaps the best way to handle the military time issue is to add at the beginning of the code a prompt to the user with a radio button for am or pm. The radio button would default to am for the "start time" column and to pm for the "end time" column. The calculations would then proceed based on the selection. The user would then enter "830" and then hit enter twice or enter once with a cursor move. What do you think and is it possible to add the radio button as I've described? -- CJM "CJM" wrote: Doug - Thanks so much for your thorough response. I really appreciate it. I'm in the process of trying it now and will let you know. The reason for avoiding the colon is that the payroll clerks are quite error prone, and if they have to enter a colon, that requires two entries, the shift and then the colon, hence more chance for error. Many of the clerks are good at using the number pad, but if they have to stop to enter a shift and colon, they take their hands off the number pad and hence further chance of error. I've actually seen it happen. -- CJM "Doug Glancy" wrote: CJM, I've tried to think of all the things they could enter that won't work, but I've probably missed some. Paste the following into the worksheet module. It does the conversion for any value entered into Column B. The long If statement checks for, in order, 3 or 4 characters, whether it's an integer, if the hour digits are less than or equal to 24, if the minute digits are less than or equal to 60. I tried to structure it so that you only have the error message in one place, but got a compile error on non-numerics until I put that test first. You have to enter these numbers as military time, e.g. 1430 for 2:30 pm. One weird result is that 12:30 AM must be entered as "24:30". Al in all I think I'd just have them enter the colon, but it is an interesting problem. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("B:B")) Is Nothing Then If IsNumeric(Target) Then If (Len(Target) = 3 Or Len(Target) = 4) And _ Int(Target) = Target And _ Left$(Target, WorksheetFunction.Max(Len(Target) - 2, 1)) <= 24 And _ Right$(Target, 2) <= 59 Then Target = Left$(Target, Len(Target) - 2) & ":" & Right$(Target, 2) If Left$(Target, 2) = "24" Then Target = Replace(Target, "24", "0") End If Target.NumberFormat = "h:mm AM/PM" Else Target.Clear Target.Select MsgBox "that's not a time" End If Else Target.Clear Target.Select MsgBox "that's not a time" End If End If Application.EnableEvents = True End Sub hth, Doug "CJM" wrote in message ... I'm doing something in Excel 2003 which I think is fairly basic. I'm creating a time entry worksheet function which will be used by administrators to enter the daily hours for a group of employees. Instead of entering "8:30", I would like to avoid typing the colon and have the user into "830". I would then like this to be stored and displayed in the cell as "08:30 AM". This requires a simple calculation to convert the "830" to the proper time format, but I can't figure out how to return to the active cell a calculated value which is calculated based on input from the user. I know there has to be a way. Any help would be greatly appreciated. -- CJM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user input into active excel macro- prompt - respond -insert-cont | Excel Programming | |||
Copy cell data from workbook based on user input | Excel Programming | |||
Changing Cell Contents Based Upon User Input | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming | |||
select data based on user input | Excel Programming |