Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Rows based on Time Selection in timesheet.
thanks for looking @ this.
Is it possible too. I have a timesheet that users use everyday. It has a column that starts @ 5:00am and goes to 12:00 Midnight. Each Row respresents a Tenth of an hour. In two other cells I have a "Arrival TIME" and " Departure TIME" .. Normally they start @ 8:00am, leave @ 5:00pm .. Both these are DataValidation List using the same 5:00am to 12:00am list. Is it possible too, based on the users selection of ARRIVAL time, to HIDE ROWS in the TIME COLUMN that are less than the ARRIVAL Time? .. if the user comes in @ 8:00 am, it would hide rows from 5:00am to 7:50am .. I would like to do the same with Departure Time.. If the user leaves @ 5:00pm .. it would hide from 5:10pm to 12:00 midnight.. Basically to "shorten" the timesheet up to the hours they are working. I hope I have explained this correclty. Thanks for you help. thom |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Rows based on Time Selection in timesheet.
You use a new sheet for each day?
Assume time column is column A and times start in cell A2 Dim rng as Range, res as Variant, res1 as Variant set rng = Range(A2,cells(rows.count,1).End(xlup)) Set StartTime = Range("F9") set EndTime = Range("F10") res = Application.Match(StartTime,rng,0) res1 = application.Match(EndTime,rng,0) if not iserror(res) then rows(2).Resize(res).Entirerow.Hidden = True end if if not iserror(res1) then range(rng(res1).offset(1,0),rng(rng.count)).Entire Row.Hidden = True End if -- Regards, Tom Ogilvy "thom hoyle" wrote in message ... thanks for looking @ this. Is it possible too. I have a timesheet that users use everyday. It has a column that starts @ 5:00am and goes to 12:00 Midnight. Each Row respresents a Tenth of an hour. In two other cells I have a "Arrival TIME" and " Departure TIME" .. Normally they start @ 8:00am, leave @ 5:00pm .. Both these are DataValidation List using the same 5:00am to 12:00am list. Is it possible too, based on the users selection of ARRIVAL time, to HIDE ROWS in the TIME COLUMN that are less than the ARRIVAL Time? .. if the user comes in @ 8:00 am, it would hide rows from 5:00am to 7:50am .. I would like to do the same with Departure Time.. If the user leaves @ 5:00pm .. it would hide from 5:10pm to 12:00 midnight.. Basically to "shorten" the timesheet up to the hours they are working. I hope I have explained this correclty. Thanks for you help. thom |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Rows based on Time Selection in timesheet.
private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Long On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A259")) Is Nothing Then With Target iRow = .row Me.Rows("1:259").Hidden = False Me.Rows("1:" & iRow - 1).Hidden = True End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. How do you propose toi unhide them? -- HTH RP (remove nothere from the email address if mailing direct) "thom hoyle" wrote in message ... thanks for looking @ this. Is it possible too. I have a timesheet that users use everyday. It has a column that starts @ 5:00am and goes to 12:00 Midnight. Each Row respresents a Tenth of an hour. In two other cells I have a "Arrival TIME" and " Departure TIME" .. Normally they start @ 8:00am, leave @ 5:00pm .. Both these are DataValidation List using the same 5:00am to 12:00am list. Is it possible too, based on the users selection of ARRIVAL time, to HIDE ROWS in the TIME COLUMN that are less than the ARRIVAL Time? .. if the user comes in @ 8:00 am, it would hide rows from 5:00am to 7:50am .. I would like to do the same with Departure Time.. If the user leaves @ 5:00pm .. it would hide from 5:10pm to 12:00 midnight.. Basically to "shorten" the timesheet up to the hours they are working. I hope I have explained this correclty. Thanks for you help. thom |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Rows based on Time Selection in timesheet.
Thanks for the reply..
question? I assume this is a "private Sub Worksheet_Change(ByVal Target As Range)" .. How do I add this code to an already existing "private Sub Worksheet_Change" ... that is on the sheet? ... Also, here is the actual Cell references... Range is B15:B204 Set StartTime = Range("H4") set EndTime = Range("H5") Thanks... "Tom Ogilvy" wrote: You use a new sheet for each day? Assume time column is column A and times start in cell A2 Dim rng as Range, res as Variant, res1 as Variant set rng = Range(A2,cells(rows.count,1).End(xlup)) Set StartTime = Range("F9") set EndTime = Range("F10") res = Application.Match(StartTime,rng,0) res1 = application.Match(EndTime,rng,0) if not iserror(res) then rows(2).Resize(res).Entirerow.Hidden = True end if if not iserror(res1) then range(rng(res1).offset(1,0),rng(rng.count)).Entire Row.Hidden = True End if -- Regards, Tom Ogilvy "thom hoyle" wrote in message ... thanks for looking @ this. Is it possible too. I have a timesheet that users use everyday. It has a column that starts @ 5:00am and goes to 12:00 Midnight. Each Row respresents a Tenth of an hour. In two other cells I have a "Arrival TIME" and " Departure TIME" .. Normally they start @ 8:00am, leave @ 5:00pm .. Both these are DataValidation List using the same 5:00am to 12:00am list. Is it possible too, based on the users selection of ARRIVAL time, to HIDE ROWS in the TIME COLUMN that are less than the ARRIVAL Time? .. if the user comes in @ 8:00 am, it would hide rows from 5:00am to 7:50am .. I would like to do the same with Departure Time.. If the user leaves @ 5:00pm .. it would hide from 5:10pm to 12:00 midnight.. Basically to "shorten" the timesheet up to the hours they are working. I hope I have explained this correclty. Thanks for you help. thom |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Rows based on Time Selection in timesheet.
Tom, I also get a
Run-time Error 1004 Method 'Range' of object'_Worksheet' failed on line: set rng = Range(B15,cells(rows.count,1).End(xlup)) thanks... I was also trying to run as a Macro.. "Tom Ogilvy" wrote: You use a new sheet for each day? Assume time column is column A and times start in cell A2 Dim rng as Range, res as Variant, res1 as Variant set rng = Range(A2,cells(rows.count,1).End(xlup)) Set StartTime = Range("F9") set EndTime = Range("F10") res = Application.Match(StartTime,rng,0) res1 = application.Match(EndTime,rng,0) if not iserror(res) then rows(2).Resize(res).Entirerow.Hidden = True end if if not iserror(res1) then range(rng(res1).offset(1,0),rng(rng.count)).Entire Row.Hidden = True End if -- Regards, Tom Ogilvy "thom hoyle" wrote in message ... thanks for looking @ this. Is it possible too. I have a timesheet that users use everyday. It has a column that starts @ 5:00am and goes to 12:00 Midnight. Each Row respresents a Tenth of an hour. In two other cells I have a "Arrival TIME" and " Departure TIME" .. Normally they start @ 8:00am, leave @ 5:00pm .. Both these are DataValidation List using the same 5:00am to 12:00am list. Is it possible too, based on the users selection of ARRIVAL time, to HIDE ROWS in the TIME COLUMN that are less than the ARRIVAL Time? .. if the user comes in @ 8:00 am, it would hide rows from 5:00am to 7:50am .. I would like to do the same with Departure Time.. If the user leaves @ 5:00pm .. it would hide from 5:10pm to 12:00 midnight.. Basically to "shorten" the timesheet up to the hours they are working. I hope I have explained this correclty. Thanks for you help. thom |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Rows based on Time Selection in timesheet.
Oh, and yes. We use a new sheet for each day.. Do you have any thoughts on
this. "Tom Ogilvy" wrote: You use a new sheet for each day? Assume time column is column A and times start in cell A2 Dim rng as Range, res as Variant, res1 as Variant set rng = Range(A2,cells(rows.count,1).End(xlup)) Set StartTime = Range("F9") set EndTime = Range("F10") res = Application.Match(StartTime,rng,0) res1 = application.Match(EndTime,rng,0) if not iserror(res) then rows(2).Resize(res).Entirerow.Hidden = True end if if not iserror(res1) then range(rng(res1).offset(1,0),rng(rng.count)).Entire Row.Hidden = True End if -- Regards, Tom Ogilvy "thom hoyle" wrote in message ... thanks for looking @ this. Is it possible too. I have a timesheet that users use everyday. It has a column that starts @ 5:00am and goes to 12:00 Midnight. Each Row respresents a Tenth of an hour. In two other cells I have a "Arrival TIME" and " Departure TIME" .. Normally they start @ 8:00am, leave @ 5:00pm .. Both these are DataValidation List using the same 5:00am to 12:00am list. Is it possible too, based on the users selection of ARRIVAL time, to HIDE ROWS in the TIME COLUMN that are less than the ARRIVAL Time? .. if the user comes in @ 8:00 am, it would hide rows from 5:00am to 7:50am .. I would like to do the same with Departure Time.. If the user leaves @ 5:00pm .. it would hide from 5:10pm to 12:00 midnight.. Basically to "shorten" the timesheet up to the hours they are working. I hope I have explained this correclty. Thanks for you help. thom |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Rows based on Time Selection in timesheet.
set rng = Range("B15",cells(rows.count,1).End(xlup))
My typo. No, it isn't a change event, I assumed this was a onetime thing. If you want to react whenever someone makes an entry, you might try Bob's code. -- Regards, Tom Ogilvy "thom hoyle" wrote in message ... Tom, I also get a Run-time Error 1004 Method 'Range' of object'_Worksheet' failed on line: set rng = Range(B15,cells(rows.count,1).End(xlup)) thanks... I was also trying to run as a Macro.. "Tom Ogilvy" wrote: You use a new sheet for each day? Assume time column is column A and times start in cell A2 Dim rng as Range, res as Variant, res1 as Variant set rng = Range(A2,cells(rows.count,1).End(xlup)) Set StartTime = Range("F9") set EndTime = Range("F10") res = Application.Match(StartTime,rng,0) res1 = application.Match(EndTime,rng,0) if not iserror(res) then rows(2).Resize(res).Entirerow.Hidden = True end if if not iserror(res1) then range(rng(res1).offset(1,0),rng(rng.count)).Entire Row.Hidden = True End if -- Regards, Tom Ogilvy "thom hoyle" wrote in message ... thanks for looking @ this. Is it possible too. I have a timesheet that users use everyday. It has a column that starts @ 5:00am and goes to 12:00 Midnight. Each Row respresents a Tenth of an hour. In two other cells I have a "Arrival TIME" and " Departure TIME" .. Normally they start @ 8:00am, leave @ 5:00pm .. Both these are DataValidation List using the same 5:00am to 12:00am list. Is it possible too, based on the users selection of ARRIVAL time, to HIDE ROWS in the TIME COLUMN that are less than the ARRIVAL Time? .. if the user comes in @ 8:00 am, it would hide rows from 5:00am to 7:50am .. I would like to do the same with Departure Time.. If the user leaves @ 5:00pm .. it would hide from 5:10pm to 12:00 midnight.. Basically to "shorten" the timesheet up to the hours they are working. I hope I have explained this correclty. Thanks for you help. thom |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Rows based on Time Selection in timesheet.
I get a compile error that "Variable not defined".. StartTime..
help.. thanks "Tom Ogilvy" wrote: set rng = Range("B15",cells(rows.count,1).End(xlup)) My typo. No, it isn't a change event, I assumed this was a onetime thing. If you want to react whenever someone makes an entry, you might try Bob's code. -- Regards, Tom Ogilvy "thom hoyle" wrote in message ... Tom, I also get a Run-time Error 1004 Method 'Range' of object'_Worksheet' failed on line: set rng = Range(B15,cells(rows.count,1).End(xlup)) thanks... I was also trying to run as a Macro.. "Tom Ogilvy" wrote: You use a new sheet for each day? Assume time column is column A and times start in cell A2 Dim rng as Range, res as Variant, res1 as Variant set rng = Range(A2,cells(rows.count,1).End(xlup)) Set StartTime = Range("F9") set EndTime = Range("F10") res = Application.Match(StartTime,rng,0) res1 = application.Match(EndTime,rng,0) if not iserror(res) then rows(2).Resize(res).Entirerow.Hidden = True end if if not iserror(res1) then range(rng(res1).offset(1,0),rng(rng.count)).Entire Row.Hidden = True End if -- Regards, Tom Ogilvy "thom hoyle" wrote in message ... thanks for looking @ this. Is it possible too. I have a timesheet that users use everyday. It has a column that starts @ 5:00am and goes to 12:00 Midnight. Each Row respresents a Tenth of an hour. In two other cells I have a "Arrival TIME" and " Departure TIME" .. Normally they start @ 8:00am, leave @ 5:00pm .. Both these are DataValidation List using the same 5:00am to 12:00am list. Is it possible too, based on the users selection of ARRIVAL time, to HIDE ROWS in the TIME COLUMN that are less than the ARRIVAL Time? .. if the user comes in @ 8:00 am, it would hide rows from 5:00am to 7:50am .. I would like to do the same with Departure Time.. If the user leaves @ 5:00pm .. it would hide from 5:10pm to 12:00 midnight.. Basically to "shorten" the timesheet up to the hours they are working. I hope I have explained this correclty. Thanks for you help. thom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Rows based on value, but not using autofilter | Excel Worksheet Functions | |||
how can I unhide/hide a sheet based on pull down selection? | Excel Discussion (Misc queries) | |||
Hide row(s) based on drop down selection | Excel Discussion (Misc queries) | |||
Hide Rows based on value | Excel Discussion (Misc queries) | |||
Moving Selection and Time based run macro | Excel Programming |