Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CurrentRegion Failure. Please help!
This problem is still stumping me. Tom Ogilvy led me in the right direction
but my code is acting funny. I have form code that adds data to a sheet and it taketh away as the user requests. Each time a change is made, the sub below "Updateform" is called to reinitialize the forms listboxes to reflect those change. This sub works perfectly at all but one time. When I delete an item from the sheet, say "A4", and when the sub below is called, the failure occurs when rNameRange = Sheets("Names").Cells(1, 1).CurrentRegion.Columns(1) is called. I get a 1004 error "Unable to get the current region property of the range class". Tom suggested to make sure that the correct sheet is activated as well as the cell "A1" is activated. I have tried adding the following lines: Range("A1"). select selection.clear To top that off, "activecell.address" was added to the watch window, and it constantly reflects cell A4, even after the line range("A1").activate I can't figure this out and it is holding me up from continuing. Please help me. I can send all the code if you wish Here goes: Private Sub UpdateForm() Dim rNameRange As Object Dim CurrentRecord As Integer ThisWorkbook.Sheets("Names").range("A1").Activate 'the next line is the problem although it works some of the time Set rNameRange = Sheets("Names").Cells(1, 1).CurrentRegion.Columns(1) RepEntryBox.RowSource = rNameRange.Address ListOfRepsBox.RowSource = rNameRange.Address ThisWorkbook.Sheets("Stores").Activate If Me.ListOfRepsBox.ListIndex < 0 Then Me.RepStoreList.Clear Exit Sub End If CurrentRecord = Me.ListOfRepsBox.ListIndex + 1 Set rNameRange = Sheets("Stores").Cells(1, CurrentRecord).CurrentRegion.Columns(CurrentRecord ) Me.RepStoreList.RowSource = rNameRange.Address End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CurrentRegion Failure. Please help!
"John T Ingato" wrote in message
... Private Sub UpdateForm() Dim rNameRange As Object Why not declare rNameRange as a Range object explicitly: Dim rNameRange as Range |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CurrentRegion Failure. Please help!
My mistake in not informin you. I have had it as Dim rNameRange as range
I just tried setting it as object today. I am trying anything at this point. "Michael Hopwood" wrote in message ... "John T Ingato" wrote in message ... Private Sub UpdateForm() Dim rNameRange As Object Why not declare rNameRange as a Range object explicitly: Dim rNameRange as Range |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup failure & ctrl-f failure? | Excel Discussion (Misc queries) | |||
currentregion | Excel Discussion (Misc queries) | |||
Formula equivalent of CurrentRegion | Excel Worksheet Functions | |||
CurrentRegion.copy maybe? | Excel Programming |