Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
V.B selecting ranges for naming
HI, i have a problem with a worksheet_change event that i am trying t
put together and was wondering if anyone could help me. This is what i have at the moment. VB: -------------------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) ' Hide operations from the user Application.ScreenUpdating = False Sheets("workers").Select Range("D3").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="worker_code", RefersToR1C1:= _ "=Workers!R3C4:R14C4" Range("D2").Select -------------------------------------------------------------------------------- what i want it to do is rename a range of cells with data in them. But i dont know how to make the line which says VB: -------------------------------------------------------------------------------- ActiveWorkbook.Names.Add Name:="worker_code", RefersToR1C1:= _ "=Workers!R3C4:R14C4" -------------------------------------------------------------------------------- do the same procedure as while re-naming that range. VB: -------------------------------------------------------------------------------- Range(Selection, Selection.End(xlDown)).Select -------------------------------------------------------------------------------- I hope i have given you enough information to help you understand m problem. Please any help would be greatly appreciated -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
V.B selecting ranges for naming
Is this what you mean
Sheets("workers").Range("D3",Range("D3").End(xlDow n)).Name:="worker_code Range("D2").Select -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ice_cool " wrote in message ... HI, i have a problem with a worksheet_change event that i am trying to put together and was wondering if anyone could help me. This is what i have at the moment. VB: -------------------------------------------------------------------------- ------ Private Sub Worksheet_Change(ByVal Target As Range) ' Hide operations from the user Application.ScreenUpdating = False Sheets("workers").Select Range("D3").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="worker_code", RefersToR1C1:= _ "=Workers!R3C4:R14C4" Range("D2").Select -------------------------------------------------------------------------- ------ what i want it to do is rename a range of cells with data in them. But i dont know how to make the line which says VB: -------------------------------------------------------------------------- ------ ActiveWorkbook.Names.Add Name:="worker_code", RefersToR1C1:= _ "=Workers!R3C4:R14C4" -------------------------------------------------------------------------- ------ do the same procedure as while re-naming that range. VB: -------------------------------------------------------------------------- ------ Range(Selection, Selection.End(xlDown)).Select -------------------------------------------------------------------------- ------ I hope i have given you enough information to help you understand my problem. Please any help would be greatly appreciated. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
V.B selecting ranges for naming
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("workers") .Range(.Range("D3"), _ .Range("D3").End(xlDown)).Name _ = "worker_code" End With End Sub works for me and is hidden from the user. Or to use Bob's approach Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Sheets("workers").Select Sheets("workers").Range("D3", _ Sheets("workers").Range("D3") _ .End(xlDown)).Name = "worker_code" Sheets("workers").Range("D2").Select Application.ScreenUpdating = True End Sub You need to qualify entries on the active sheet since this is in the sheet code of a different sheet. -- Regards, Tom Ogilvy "ice_cool " wrote in message ... HI, i have a problem with a worksheet_change event that i am trying to put together and was wondering if anyone could help me. This is what i have at the moment. VB: -------------------------------------------------------------------------- ------ Private Sub Worksheet_Change(ByVal Target As Range) ' Hide operations from the user Application.ScreenUpdating = False Sheets("workers").Select Range("D3").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="worker_code", RefersToR1C1:= _ "=Workers!R3C4:R14C4" Range("D2").Select -------------------------------------------------------------------------- ------ what i want it to do is rename a range of cells with data in them. But i dont know how to make the line which says VB: -------------------------------------------------------------------------- ------ ActiveWorkbook.Names.Add Name:="worker_code", RefersToR1C1:= _ "=Workers!R3C4:R14C4" -------------------------------------------------------------------------- ------ do the same procedure as while re-naming that range. VB: -------------------------------------------------------------------------- ------ Range(Selection, Selection.End(xlDown)).Select -------------------------------------------------------------------------- ------ I hope i have given you enough information to help you understand my problem. Please any help would be greatly appreciated. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming ranges | Setting up and Configuration of Excel | |||
Naming Ranges | Excel Discussion (Misc queries) | |||
naming ranges | Excel Worksheet Functions | |||
naming ranges | Excel Worksheet Functions | |||
Naming ranges? | Excel Discussion (Misc queries) |