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

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

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