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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
Thanks, Doug. Once again you've been a big help. I couldn't get the code to
run properly, but I'm sure it wasn't your code. Since I'm not that up to speed (yet!), I have problems navigating the debugger. Sometimes I can't seem to reset the environment to start fresh, and I wind up exitng out of Excel and back in. This happened even when I got the code to work. Since I'm under a tight deadline, I've resdesigned the way I'm doing this thing. I'm setting up a form (not an actual user form, but a group of cells) that the user can enter their numbers into. I'm then converting those numbers to the format I need in another cell. The user interface looks something like this: Day Date Start AM/PM End AM/PM Code SAT 9/12 830 AM 530 PM 8 SUN 9/13 830 AM 530 PM 8 MON 9/14 830 AM 530 PM 8 TUE 9/15 830 AM 530 PM 8 This will work and everyone seems to like this idea. The problem I'm having now is I'd like to be able to navigate the user input -- i.e., when the user gets to the end of the first row, I'd like the cell selection to be the first entry in the next row so that the user doesn't have to cursor over or click in the cell. This seems easy to me, and I assume it will require a macro. Have you ever done this? I thought of setting up a user form, but I think this might be too complicated. What do you think? People like you keep me going...I'm really tired and I didn't get to have lunch! -- CJM "Doug Glancy" wrote: CJM, The issue that I see with only triggering if the cell is blank is that if the user realizes they meant 730 instead of 830 the code won't run when they make the corrcection. Here's what I've come up with. It's convoluted and my lunch is almost over, so see if it works for you and feel free to ask me what I meant either way. This now assumes that start time is in column B and end time is in column C. I added code to not do any processing on cells outside columns B and C (in the event that they change multiple cells at once, some inside range B:C and some outside: Private Sub Worksheet_Change(ByVal target As Range) Dim target_cell As Range Application.EnableEvents = False If Not Intersect(target, Range("B:C")) Is Nothing Then For Each target_cell In Intersect(target, Range("B:C")) If IsNumeric(target_cell) Then If Not (IsDate(Date & " " & Format(target_cell, "h:mm")) And Int(target_cell) = 0) Then If (Len(target_cell) = 3 Or Len(target_cell) = 4) And _ Int(target_cell) = target_cell And _ Left$(target_cell, WorksheetFunction.Max(Len(target_cell) - 2, 1)) <= 12 And _ Right$(target_cell, 2) <= 59 Then target_cell = Left$(target_cell, Len(target_cell) - 2) & ":" & Right$(target_cell, 2) & " AM" If Not Intersect(target_cell, Range("C:C")) Is Nothing Then target_cell = Replace(target_cell.Text, "AM", "PM") End If target_cell.NumberFormat = "h:mm AM/PM" Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If End If Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If Next target_cell End If Application.EnableEvents = True End Sub hth, Doug "CJM" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
CJM,
For your tabbing question, try something like this. I assume that your data entry area spans columns A:F: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F:F")) Is Nothing Then Range("A" & Target.Row + 1).Select End If End Sub Also, take a look at setting up a data list (Data List, available in 2003, not sure about XP and earlier) and see if that meets your purposes. It's a basic data entry environment that allows users to add new rows among other things and tabs as you described. hth, Doug "CJM" wrote in message ... Thanks, Doug. Once again you've been a big help. I couldn't get the code to run properly, but I'm sure it wasn't your code. Since I'm not that up to speed (yet!), I have problems navigating the debugger. Sometimes I can't seem to reset the environment to start fresh, and I wind up exitng out of Excel and back in. This happened even when I got the code to work. Since I'm under a tight deadline, I've resdesigned the way I'm doing this thing. I'm setting up a form (not an actual user form, but a group of cells) that the user can enter their numbers into. I'm then converting those numbers to the format I need in another cell. The user interface looks something like this: Day Date Start AM/PM End AM/PM Code SAT 9/12 830 AM 530 PM 8 SUN 9/13 830 AM 530 PM 8 MON 9/14 830 AM 530 PM 8 TUE 9/15 830 AM 530 PM 8 This will work and everyone seems to like this idea. The problem I'm having now is I'd like to be able to navigate the user input -- i.e., when the user gets to the end of the first row, I'd like the cell selection to be the first entry in the next row so that the user doesn't have to cursor over or click in the cell. This seems easy to me, and I assume it will require a macro. Have you ever done this? I thought of setting up a user form, but I think this might be too complicated. What do you think? People like you keep me going...I'm really tired and I didn't get to have lunch! -- CJM "Doug Glancy" wrote: CJM, The issue that I see with only triggering if the cell is blank is that if the user realizes they meant 730 instead of 830 the code won't run when they make the corrcection. Here's what I've come up with. It's convoluted and my lunch is almost over, so see if it works for you and feel free to ask me what I meant either way. This now assumes that start time is in column B and end time is in column C. I added code to not do any processing on cells outside columns B and C (in the event that they change multiple cells at once, some inside range B:C and some outside: Private Sub Worksheet_Change(ByVal target As Range) Dim target_cell As Range Application.EnableEvents = False If Not Intersect(target, Range("B:C")) Is Nothing Then For Each target_cell In Intersect(target, Range("B:C")) If IsNumeric(target_cell) Then If Not (IsDate(Date & " " & Format(target_cell, "h:mm")) And Int(target_cell) = 0) Then If (Len(target_cell) = 3 Or Len(target_cell) = 4) And _ Int(target_cell) = target_cell And _ Left$(target_cell, WorksheetFunction.Max(Len(target_cell) - 2, 1)) <= 12 And _ Right$(target_cell, 2) <= 59 Then target_cell = Left$(target_cell, Len(target_cell) - 2) & ":" & Right$(target_cell, 2) & " AM" If Not Intersect(target_cell, Range("C:C")) Is Nothing Then target_cell = Replace(target_cell.Text, "AM", "PM") End If target_cell.NumberFormat = "h:mm AM/PM" Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If End If Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If Next target_cell End If Application.EnableEvents = True End Sub hth, Doug "CJM" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
It worked like a charm! I can't thank you enough. I'm slowly by surely
starting to get the hang of this. I figured out the debugger thing. Thanks to your code, I have been able to figure out how to do certain things. I see how you coded something, and I apply it to another component. You've been a tremendous help. -- CJM "Doug Glancy" wrote: CJM, For your tabbing question, try something like this. I assume that your data entry area spans columns A:F: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F:F")) Is Nothing Then Range("A" & Target.Row + 1).Select End If End Sub Also, take a look at setting up a data list (Data List, available in 2003, not sure about XP and earlier) and see if that meets your purposes. It's a basic data entry environment that allows users to add new rows among other things and tabs as you described. hth, Doug "CJM" wrote in message ... Thanks, Doug. Once again you've been a big help. I couldn't get the code to run properly, but I'm sure it wasn't your code. Since I'm not that up to speed (yet!), I have problems navigating the debugger. Sometimes I can't seem to reset the environment to start fresh, and I wind up exitng out of Excel and back in. This happened even when I got the code to work. Since I'm under a tight deadline, I've resdesigned the way I'm doing this thing. I'm setting up a form (not an actual user form, but a group of cells) that the user can enter their numbers into. I'm then converting those numbers to the format I need in another cell. The user interface looks something like this: Day Date Start AM/PM End AM/PM Code SAT 9/12 830 AM 530 PM 8 SUN 9/13 830 AM 530 PM 8 MON 9/14 830 AM 530 PM 8 TUE 9/15 830 AM 530 PM 8 This will work and everyone seems to like this idea. The problem I'm having now is I'd like to be able to navigate the user input -- i.e., when the user gets to the end of the first row, I'd like the cell selection to be the first entry in the next row so that the user doesn't have to cursor over or click in the cell. This seems easy to me, and I assume it will require a macro. Have you ever done this? I thought of setting up a user form, but I think this might be too complicated. What do you think? People like you keep me going...I'm really tired and I didn't get to have lunch! -- CJM "Doug Glancy" wrote: CJM, The issue that I see with only triggering if the cell is blank is that if the user realizes they meant 730 instead of 830 the code won't run when they make the corrcection. Here's what I've come up with. It's convoluted and my lunch is almost over, so see if it works for you and feel free to ask me what I meant either way. This now assumes that start time is in column B and end time is in column C. I added code to not do any processing on cells outside columns B and C (in the event that they change multiple cells at once, some inside range B:C and some outside: Private Sub Worksheet_Change(ByVal target As Range) Dim target_cell As Range Application.EnableEvents = False If Not Intersect(target, Range("B:C")) Is Nothing Then For Each target_cell In Intersect(target, Range("B:C")) If IsNumeric(target_cell) Then If Not (IsDate(Date & " " & Format(target_cell, "h:mm")) And Int(target_cell) = 0) Then If (Len(target_cell) = 3 Or Len(target_cell) = 4) And _ Int(target_cell) = target_cell And _ Left$(target_cell, WorksheetFunction.Max(Len(target_cell) - 2, 1)) <= 12 And _ Right$(target_cell, 2) <= 59 Then target_cell = Left$(target_cell, Len(target_cell) - 2) & ":" & Right$(target_cell, 2) & " AM" If Not Intersect(target_cell, Range("C:C")) Is Nothing Then target_cell = Replace(target_cell.Text, "AM", "PM") End If target_cell.NumberFormat = "h:mm AM/PM" Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If End If Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If Next target_cell End If Application.EnableEvents = True End Sub hth, Doug "CJM" wrote in message ... 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, |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
CJM,
You're very welcome. It was a learning experience for me too! Doug "CJM" wrote in message ... It worked like a charm! I can't thank you enough. I'm slowly by surely starting to get the hang of this. I figured out the debugger thing. Thanks to your code, I have been able to figure out how to do certain things. I see how you coded something, and I apply it to another component. You've been a tremendous help. -- CJM "Doug Glancy" wrote: CJM, For your tabbing question, try something like this. I assume that your data entry area spans columns A:F: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F:F")) Is Nothing Then Range("A" & Target.Row + 1).Select End If End Sub Also, take a look at setting up a data list (Data List, available in 2003, not sure about XP and earlier) and see if that meets your purposes. It's a basic data entry environment that allows users to add new rows among other things and tabs as you described. hth, Doug "CJM" wrote in message ... Thanks, Doug. Once again you've been a big help. I couldn't get the code to run properly, but I'm sure it wasn't your code. Since I'm not that up to speed (yet!), I have problems navigating the debugger. Sometimes I can't seem to reset the environment to start fresh, and I wind up exitng out of Excel and back in. This happened even when I got the code to work. Since I'm under a tight deadline, I've resdesigned the way I'm doing this thing. I'm setting up a form (not an actual user form, but a group of cells) that the user can enter their numbers into. I'm then converting those numbers to the format I need in another cell. The user interface looks something like this: Day Date Start AM/PM End AM/PM Code SAT 9/12 830 AM 530 PM 8 SUN 9/13 830 AM 530 PM 8 MON 9/14 830 AM 530 PM 8 TUE 9/15 830 AM 530 PM 8 This will work and everyone seems to like this idea. The problem I'm having now is I'd like to be able to navigate the user input -- i.e., when the user gets to the end of the first row, I'd like the cell selection to be the first entry in the next row so that the user doesn't have to cursor over or click in the cell. This seems easy to me, and I assume it will require a macro. Have you ever done this? I thought of setting up a user form, but I think this might be too complicated. What do you think? People like you keep me going...I'm really tired and I didn't get to have lunch! -- CJM "Doug Glancy" wrote: CJM, The issue that I see with only triggering if the cell is blank is that if the user realizes they meant 730 instead of 830 the code won't run when they make the corrcection. Here's what I've come up with. It's convoluted and my lunch is almost over, so see if it works for you and feel free to ask me what I meant either way. This now assumes that start time is in column B and end time is in column C. I added code to not do any processing on cells outside columns B and C (in the event that they change multiple cells at once, some inside range B:C and some outside: Private Sub Worksheet_Change(ByVal target As Range) Dim target_cell As Range Application.EnableEvents = False If Not Intersect(target, Range("B:C")) Is Nothing Then For Each target_cell In Intersect(target, Range("B:C")) If IsNumeric(target_cell) Then If Not (IsDate(Date & " " & Format(target_cell, "h:mm")) And Int(target_cell) = 0) Then If (Len(target_cell) = 3 Or Len(target_cell) = 4) And _ Int(target_cell) = target_cell And _ Left$(target_cell, WorksheetFunction.Max(Len(target_cell) - 2, 1)) <= 12 And _ Right$(target_cell, 2) <= 59 Then target_cell = Left$(target_cell, Len(target_cell) - 2) & ":" & Right$(target_cell, 2) & " AM" If Not Intersect(target_cell, Range("C:C")) Is Nothing Then target_cell = Replace(target_cell.Text, "AM", "PM") End If target_cell.NumberFormat = "h:mm AM/PM" Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If End If Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If Next target_cell End If Application.EnableEvents = True End Sub hth, Doug "CJM" wrote in message ... 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, |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
Hello! Maybe you can explain something to me. I am using the following code
(which I got from the discussion group) for my program. It works beautifully; however, there's one part of it I don't understand. I've tested it many times and did receive an error (can't remember what kind), but I think that was due to bad data. All it's doing is taking a range on one page in a workbook and copying it to the end of another page using paste special values. The program I created allows payroll clerks to enter time data and perform calculations for multiple employees (sometimes up to hundreds of employees). When they're finished with one employee, they click a button in the program and the information is copied to the end of another page so that when the clerks are through entering data, there is one compiled list of all the employees' data. I then create a csv of that file and it gets uploaded. I was given 1 week to come up with the system, and that's the best I could do. My client is using excel to bypass getting a real database system which they need. Anyway, here's the code: 'Export Data ActiveWindow.ScrollWorkbookTabs Sheets:=1 Sheets("Summary Totals").Select Sheets("Summary Totals").Range("A9:O15").Select Selection.Copy Sheets("Compiled Totals").Select Sheets("Compiled Totals").Range("A9").Select Do Until ActiveCell.Offset(0, 1).Value = "" If ActiveCell.Value < "" Then ActiveCell.Offset(1, 0).Range("A1").Select Else ActiveCell.Offset(1, 0).Range("A1").Select End If Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Field Rep Time Sheet").Select Range("A19").Select The part I don't understand is the If statement in the middle: If ActiveCell.Value < "" Then ActiveCell.Offset(1, 0).Range("A1").Select Else ActiveCell.Offset(1, 0).Range("A1").Select End If The If then and Else statements are exactly the same. Do you understand what it's doing? Also, while I have your ear, what's the easiest way to identify a range of non blank entries -- i.e., from the compiled file I created, I need to export that data to a csv file (which is another issue), but I'm having problems selecting the range with nonblank data. I don't want to select all as I only want to send non blank rows. Anyway, if you're not busy during lunch and if I'm not taking advantage, I'd love a response. Thanks! :) -- CJM "Doug Glancy" wrote: CJM, You're very welcome. It was a learning experience for me too! Doug "CJM" wrote in message ... It worked like a charm! I can't thank you enough. I'm slowly by surely starting to get the hang of this. I figured out the debugger thing. Thanks to your code, I have been able to figure out how to do certain things. I see how you coded something, and I apply it to another component. You've been a tremendous help. -- CJM "Doug Glancy" wrote: CJM, For your tabbing question, try something like this. I assume that your data entry area spans columns A:F: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F:F")) Is Nothing Then Range("A" & Target.Row + 1).Select End If End Sub Also, take a look at setting up a data list (Data List, available in 2003, not sure about XP and earlier) and see if that meets your purposes. It's a basic data entry environment that allows users to add new rows among other things and tabs as you described. hth, Doug "CJM" wrote in message ... Thanks, Doug. Once again you've been a big help. I couldn't get the code to run properly, but I'm sure it wasn't your code. Since I'm not that up to speed (yet!), I have problems navigating the debugger. Sometimes I can't seem to reset the environment to start fresh, and I wind up exitng out of Excel and back in. This happened even when I got the code to work. Since I'm under a tight deadline, I've resdesigned the way I'm doing this thing. I'm setting up a form (not an actual user form, but a group of cells) that the user can enter their numbers into. I'm then converting those numbers to the format I need in another cell. The user interface looks something like this: Day Date Start AM/PM End AM/PM Code SAT 9/12 830 AM 530 PM 8 SUN 9/13 830 AM 530 PM 8 MON 9/14 830 AM 530 PM 8 TUE 9/15 830 AM 530 PM 8 This will work and everyone seems to like this idea. The problem I'm having now is I'd like to be able to navigate the user input -- i.e., when the user gets to the end of the first row, I'd like the cell selection to be the first entry in the next row so that the user doesn't have to cursor over or click in the cell. This seems easy to me, and I assume it will require a macro. Have you ever done this? I thought of setting up a user form, but I think this might be too complicated. What do you think? People like you keep me going...I'm really tired and I didn't get to have lunch! -- CJM "Doug Glancy" wrote: CJM, The issue that I see with only triggering if the cell is blank is that if the user realizes they meant 730 instead of 830 the code won't run when they make the corrcection. Here's what I've come up with. It's convoluted and my lunch is almost over, so see if it works for you and feel free to ask me what I meant either way. This now assumes that start time is in column B and end time is in column C. I added code to not do any processing on cells outside columns B and C (in the event that they change multiple cells at once, some inside range B:C and some outside: Private Sub Worksheet_Change(ByVal target As Range) Dim target_cell As Range Application.EnableEvents = False If Not Intersect(target, Range("B:C")) Is Nothing Then For Each target_cell In Intersect(target, Range("B:C")) If IsNumeric(target_cell) Then If Not (IsDate(Date & " " & Format(target_cell, "h:mm")) And Int(target_cell) = 0) Then If (Len(target_cell) = 3 Or Len(target_cell) = 4) And _ Int(target_cell) = target_cell And _ Left$(target_cell, WorksheetFunction.Max(Len(target_cell) - 2, 1)) <= 12 And _ Right$(target_cell, 2) <= 59 Then target_cell = Left$(target_cell, Len(target_cell) - 2) & ":" & Right$(target_cell, 2) & " AM" If Not Intersect(target_cell, Range("C:C")) Is Nothing Then target_cell = Replace(target_cell.Text, "AM", "PM") End If target_cell.NumberFormat = "h:mm AM/PM" Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If End If Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If Next target_cell End If Application.EnableEvents = True End Sub hth, Doug "CJM" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
Hello -- one more question. I am trying to sort a list first by tech no and
then by name and am using the following code: Range("A9").Select Range("A8:O22").Sort Key1:=Range("D9"), Order1:=xlAscending, Key2:=Range( _ "B9"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal ActiveWindow.SmallScroll Down:=6 Rather than specify the range, I'd like excel to determine the range of the table. What do I put instead of the A8:O22? BTW, I got the code by recording a macro to sort and then copying the code. I'm sure everybody does that! -- CJM "Doug Glancy" wrote: CJM, You're very welcome. It was a learning experience for me too! Doug "CJM" wrote in message ... It worked like a charm! I can't thank you enough. I'm slowly by surely starting to get the hang of this. I figured out the debugger thing. Thanks to your code, I have been able to figure out how to do certain things. I see how you coded something, and I apply it to another component. You've been a tremendous help. -- CJM "Doug Glancy" wrote: CJM, For your tabbing question, try something like this. I assume that your data entry area spans columns A:F: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F:F")) Is Nothing Then Range("A" & Target.Row + 1).Select End If End Sub Also, take a look at setting up a data list (Data List, available in 2003, not sure about XP and earlier) and see if that meets your purposes. It's a basic data entry environment that allows users to add new rows among other things and tabs as you described. hth, Doug "CJM" wrote in message ... Thanks, Doug. Once again you've been a big help. I couldn't get the code to run properly, but I'm sure it wasn't your code. Since I'm not that up to speed (yet!), I have problems navigating the debugger. Sometimes I can't seem to reset the environment to start fresh, and I wind up exitng out of Excel and back in. This happened even when I got the code to work. Since I'm under a tight deadline, I've resdesigned the way I'm doing this thing. I'm setting up a form (not an actual user form, but a group of cells) that the user can enter their numbers into. I'm then converting those numbers to the format I need in another cell. The user interface looks something like this: Day Date Start AM/PM End AM/PM Code SAT 9/12 830 AM 530 PM 8 SUN 9/13 830 AM 530 PM 8 MON 9/14 830 AM 530 PM 8 TUE 9/15 830 AM 530 PM 8 This will work and everyone seems to like this idea. The problem I'm having now is I'd like to be able to navigate the user input -- i.e., when the user gets to the end of the first row, I'd like the cell selection to be the first entry in the next row so that the user doesn't have to cursor over or click in the cell. This seems easy to me, and I assume it will require a macro. Have you ever done this? I thought of setting up a user form, but I think this might be too complicated. What do you think? People like you keep me going...I'm really tired and I didn't get to have lunch! -- CJM "Doug Glancy" wrote: CJM, The issue that I see with only triggering if the cell is blank is that if the user realizes they meant 730 instead of 830 the code won't run when they make the corrcection. Here's what I've come up with. It's convoluted and my lunch is almost over, so see if it works for you and feel free to ask me what I meant either way. This now assumes that start time is in column B and end time is in column C. I added code to not do any processing on cells outside columns B and C (in the event that they change multiple cells at once, some inside range B:C and some outside: Private Sub Worksheet_Change(ByVal target As Range) Dim target_cell As Range Application.EnableEvents = False If Not Intersect(target, Range("B:C")) Is Nothing Then For Each target_cell In Intersect(target, Range("B:C")) If IsNumeric(target_cell) Then If Not (IsDate(Date & " " & Format(target_cell, "h:mm")) And Int(target_cell) = 0) Then If (Len(target_cell) = 3 Or Len(target_cell) = 4) And _ Int(target_cell) = target_cell And _ Left$(target_cell, WorksheetFunction.Max(Len(target_cell) - 2, 1)) <= 12 And _ Right$(target_cell, 2) <= 59 Then target_cell = Left$(target_cell, Len(target_cell) - 2) & ":" & Right$(target_cell, 2) & " AM" If Not Intersect(target_cell, Range("C:C")) Is Nothing Then target_cell = Replace(target_cell.Text, "AM", "PM") End If target_cell.NumberFormat = "h:mm AM/PM" Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If End If Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If Next target_cell End If Application.EnableEvents = True End Sub hth, Doug "CJM" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
CJM,
I don't understand why the If and Else are both the same. Sounds like they should just be converted to the one line. For your non-blank question I'd need a more detailed description, but see my second suggestion below. Two general suggestions your questions. First try a Google search using the Groups category - these are the archived newsgroup discussions. If you're like me you'll find that others have asked your question before and you don't have to wait for an answer. It's important to search within Groups, then you'll see a straightforward listing of threads like this one. There are web sites that use these threads as content, but they're just adding a layer. If that doesn't work, just post a new question to this group. There are far more experienced folks monitoring this and the other Excel groups (some of whom seem to sleep very little!) and by starting a new thread you're more likely to get a good answer. hth, Doug "CJM" wrote in message ... Hello! Maybe you can explain something to me. I am using the following code (which I got from the discussion group) for my program. It works beautifully; however, there's one part of it I don't understand. I've tested it many times and did receive an error (can't remember what kind), but I think that was due to bad data. All it's doing is taking a range on one page in a workbook and copying it to the end of another page using paste special values. The program I created allows payroll clerks to enter time data and perform calculations for multiple employees (sometimes up to hundreds of employees). When they're finished with one employee, they click a button in the program and the information is copied to the end of another page so that when the clerks are through entering data, there is one compiled list of all the employees' data. I then create a csv of that file and it gets uploaded. I was given 1 week to come up with the system, and that's the best I could do. My client is using excel to bypass getting a real database system which they need. Anyway, here's the code: 'Export Data ActiveWindow.ScrollWorkbookTabs Sheets:=1 Sheets("Summary Totals").Select Sheets("Summary Totals").Range("A9:O15").Select Selection.Copy Sheets("Compiled Totals").Select Sheets("Compiled Totals").Range("A9").Select Do Until ActiveCell.Offset(0, 1).Value = "" If ActiveCell.Value < "" Then ActiveCell.Offset(1, 0).Range("A1").Select Else ActiveCell.Offset(1, 0).Range("A1").Select End If Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Field Rep Time Sheet").Select Range("A19").Select The part I don't understand is the If statement in the middle: If ActiveCell.Value < "" Then ActiveCell.Offset(1, 0).Range("A1").Select Else ActiveCell.Offset(1, 0).Range("A1").Select End If The If then and Else statements are exactly the same. Do you understand what it's doing? Also, while I have your ear, what's the easiest way to identify a range of non blank entries -- i.e., from the compiled file I created, I need to export that data to a csv file (which is another issue), but I'm having problems selecting the range with nonblank data. I don't want to select all as I only want to send non blank rows. Anyway, if you're not busy during lunch and if I'm not taking advantage, I'd love a response. Thanks! :) -- CJM "Doug Glancy" wrote: CJM, You're very welcome. It was a learning experience for me too! Doug "CJM" wrote in message ... It worked like a charm! I can't thank you enough. I'm slowly by surely starting to get the hang of this. I figured out the debugger thing. Thanks to your code, I have been able to figure out how to do certain things. I see how you coded something, and I apply it to another component. You've been a tremendous help. -- CJM "Doug Glancy" wrote: CJM, For your tabbing question, try something like this. I assume that your data entry area spans columns A:F: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F:F")) Is Nothing Then Range("A" & Target.Row + 1).Select End If End Sub Also, take a look at setting up a data list (Data List, available in 2003, not sure about XP and earlier) and see if that meets your purposes. It's a basic data entry environment that allows users to add new rows among other things and tabs as you described. hth, Doug "CJM" wrote in message ... Thanks, Doug. Once again you've been a big help. I couldn't get the code to run properly, but I'm sure it wasn't your code. Since I'm not that up to speed (yet!), I have problems navigating the debugger. Sometimes I can't seem to reset the environment to start fresh, and I wind up exitng out of Excel and back in. This happened even when I got the code to work. Since I'm under a tight deadline, I've resdesigned the way I'm doing this thing. I'm setting up a form (not an actual user form, but a group of cells) that the user can enter their numbers into. I'm then converting those numbers to the format I need in another cell. The user interface looks something like this: Day Date Start AM/PM End AM/PM Code SAT 9/12 830 AM 530 PM 8 SUN 9/13 830 AM 530 PM 8 MON 9/14 830 AM 530 PM 8 TUE 9/15 830 AM 530 PM 8 This will work and everyone seems to like this idea. The problem I'm having now is I'd like to be able to navigate the user input -- i.e., when the user gets to the end of the first row, I'd like the cell selection to be the first entry in the next row so that the user doesn't have to cursor over or click in the cell. This seems easy to me, and I assume it will require a macro. Have you ever done this? I thought of setting up a user form, but I think this might be too complicated. What do you think? People like you keep me going...I'm really tired and I didn't get to have lunch! -- CJM "Doug Glancy" wrote: CJM, The issue that I see with only triggering if the cell is blank is that if the user realizes they meant 730 instead of 830 the code won't run when they make the corrcection. Here's what I've come up with. It's convoluted and my lunch is almost over, so see if it works for you and feel free to ask me what I meant either way. This now assumes that start time is in column B and end time is in column C. I added code to not do any processing on cells outside columns B and C (in the event that they change multiple cells at once, some inside range B:C and some outside: Private Sub Worksheet_Change(ByVal target As Range) Dim target_cell As Range Application.EnableEvents = False If Not Intersect(target, Range("B:C")) Is Nothing Then For Each target_cell In Intersect(target, Range("B:C")) If IsNumeric(target_cell) Then If Not (IsDate(Date & " " & Format(target_cell, "h:mm")) And Int(target_cell) = 0) Then If (Len(target_cell) = 3 Or Len(target_cell) = 4) And _ Int(target_cell) = target_cell And _ Left$(target_cell, WorksheetFunction.Max(Len(target_cell) - 2, 1)) <= 12 And _ Right$(target_cell, 2) <= 59 Then target_cell = Left$(target_cell, Len(target_cell) - 2) & ":" & Right$(target_cell, 2) & " AM" If Not Intersect(target_cell, Range("C:C")) Is Nothing Then target_cell = Replace(target_cell.Text, "AM", "PM") End If target_cell.NumberFormat = "h:mm AM/PM" Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If End If Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If Next target_cell End If Application.EnableEvents = True End Sub hth, Doug "CJM" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performing calculation in active cell based on user input
CJM,
If you're talking about a List created in the Data menu, here' s some sample code that changes the name of the first Data List on the sheet and then tells you the DL's address: Sub test() Dim ws As Worksheet Set ws = ActiveSheet ws.ListObjects(1).Name = "CJMs List" MsgBox ws.ListObjects("CJMs List").Range.Address End Sub On another note, it;s good to avoid "Select" and "Activate" in your code. The macro recorder, although a great tool for learning, uses them too much and they slow down your code. For example, in your other recent post, you could change: Sheets("Summary Totals").Select Sheets("Summary Totals").Range("A9:O15").Select Selection.Copy to: Sheets("Summary Totals").Range("A9:O15").Copy Nicer, isn't it? hth, Doug "CJM" wrote in message ... Hello -- one more question. I am trying to sort a list first by tech no and then by name and am using the following code: Range("A9").Select Range("A8:O22").Sort Key1:=Range("D9"), Order1:=xlAscending, Key2:=Range( _ "B9"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal ActiveWindow.SmallScroll Down:=6 Rather than specify the range, I'd like excel to determine the range of the table. What do I put instead of the A8:O22? BTW, I got the code by recording a macro to sort and then copying the code. I'm sure everybody does that! -- CJM "Doug Glancy" wrote: CJM, You're very welcome. It was a learning experience for me too! Doug "CJM" wrote in message ... It worked like a charm! I can't thank you enough. I'm slowly by surely starting to get the hang of this. I figured out the debugger thing. Thanks to your code, I have been able to figure out how to do certain things. I see how you coded something, and I apply it to another component. You've been a tremendous help. -- CJM "Doug Glancy" wrote: CJM, For your tabbing question, try something like this. I assume that your data entry area spans columns A:F: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F:F")) Is Nothing Then Range("A" & Target.Row + 1).Select End If End Sub Also, take a look at setting up a data list (Data List, available in 2003, not sure about XP and earlier) and see if that meets your purposes. It's a basic data entry environment that allows users to add new rows among other things and tabs as you described. hth, Doug "CJM" wrote in message ... Thanks, Doug. Once again you've been a big help. I couldn't get the code to run properly, but I'm sure it wasn't your code. Since I'm not that up to speed (yet!), I have problems navigating the debugger. Sometimes I can't seem to reset the environment to start fresh, and I wind up exitng out of Excel and back in. This happened even when I got the code to work. Since I'm under a tight deadline, I've resdesigned the way I'm doing this thing. I'm setting up a form (not an actual user form, but a group of cells) that the user can enter their numbers into. I'm then converting those numbers to the format I need in another cell. The user interface looks something like this: Day Date Start AM/PM End AM/PM Code SAT 9/12 830 AM 530 PM 8 SUN 9/13 830 AM 530 PM 8 MON 9/14 830 AM 530 PM 8 TUE 9/15 830 AM 530 PM 8 This will work and everyone seems to like this idea. The problem I'm having now is I'd like to be able to navigate the user input -- i.e., when the user gets to the end of the first row, I'd like the cell selection to be the first entry in the next row so that the user doesn't have to cursor over or click in the cell. This seems easy to me, and I assume it will require a macro. Have you ever done this? I thought of setting up a user form, but I think this might be too complicated. What do you think? People like you keep me going...I'm really tired and I didn't get to have lunch! -- CJM "Doug Glancy" wrote: CJM, The issue that I see with only triggering if the cell is blank is that if the user realizes they meant 730 instead of 830 the code won't run when they make the corrcection. Here's what I've come up with. It's convoluted and my lunch is almost over, so see if it works for you and feel free to ask me what I meant either way. This now assumes that start time is in column B and end time is in column C. I added code to not do any processing on cells outside columns B and C (in the event that they change multiple cells at once, some inside range B:C and some outside: Private Sub Worksheet_Change(ByVal target As Range) Dim target_cell As Range Application.EnableEvents = False If Not Intersect(target, Range("B:C")) Is Nothing Then For Each target_cell In Intersect(target, Range("B:C")) If IsNumeric(target_cell) Then If Not (IsDate(Date & " " & Format(target_cell, "h:mm")) And Int(target_cell) = 0) Then If (Len(target_cell) = 3 Or Len(target_cell) = 4) And _ Int(target_cell) = target_cell And _ Left$(target_cell, WorksheetFunction.Max(Len(target_cell) - 2, 1)) <= 12 And _ Right$(target_cell, 2) <= 59 Then target_cell = Left$(target_cell, Len(target_cell) - 2) & ":" & Right$(target_cell, 2) & " AM" If Not Intersect(target_cell, Range("C:C")) Is Nothing Then target_cell = Replace(target_cell.Text, "AM", "PM") End If target_cell.NumberFormat = "h:mm AM/PM" Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If End If Else target_cell.Clear target_cell.Select MsgBox "that's not a time" End If Next target_cell End If Application.EnableEvents = True End Sub hth, Doug "CJM" wrote in message ... 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 |
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 |