Cannot access range properties. Please help me. Reward Offered! Hee hee. I'm desperate.

  • Thread starter Thread starter Italian Job
  • Start date Start date
I

Italian Job

I will try to be as brief as possible.

I have a mutlipage form with 2 tabs SetUp, and Info.

Setup has a:
textbox: NameEntry
listbox: RepList
button: add
button remove

RepInfo has a:
listbox: ListOfRepsBox
listbox: RepStoreList
button: add
button remove

Note:**** "ListOfRepsBox" reflects the list of items in "RepList" on "SetUp"
page. "RepStoreList" contains a list of stores for each rep in
"ListOfRepsBox"


Names that are entered into "NameEntry" on "SetUp" are copied onto
Sheets("Names") starting at Range("A1") and also reflected in "RepList" when
the "add" button is hit or are removed from the list when the "remove"
button is hit. Each name is added to the next empty cell in that column.The
listbox is updated by calling a sub "UpdateForm", which sets the list to
their proper range. The code is below:



Private Sub UpdateForm()

Dim NameRange As range
Dim StoreRange As range
Dim record As Integer

Set NameRange = Sheets("Names").Cells(1, 1).CurrentRegion.Columns
***PROBLEM See below

If ListOfRepsBox.ListIndex < 0 Then
record = 1
Else
record = ListOfRepsBox.ListIndex + 1
End If

Set StoreRange = Sheets("Stores").Cells(1, record).CurrentRegion.Columns

RepEntryBox.RowSource = NameRange.Address
ListOfRepsBox.RowSource = NameRange.Address
RepStoreList.RowSource = StoreRange.Address

End Sub



PROBLEM
EXPLANATION:****************************************************************
**

Everything runs smoothly as should except one occation. I can open the form
add name, delete names, to my hearts content. I can even switch pages(tabs)
and back again. It is only when I click in any one of the listboxes on the
second page, return back to the first page and try to delete a name.
This is when I get the error - Run Time Error '1004 "Unable to get the
CurrentRegion property of the range class".
The odd thing is that entering this sub it appears that the activecell
cannot be changed. I have tried adding:

Thisworkbook.Sheets("Names").activate
Activecell.activate

Sheets("Names").range("A1").activate & .select

I have tried everything to chage the activecell. But it seems locked. The
sub is being called (kinda) from itself. The line in "UpdateForm()"...
ListOfRepsBox.RowSource = NameRange.Address fires the ListofRepsBox_click
routine that calls the UpdateForm() again. Is this where the problem lies???

If anyone would like to peruse the complete code in excel format, text doc,
whatever. I am more then willing to send the code and more then willing to
pay to have this explained to me. ( Quote me) I would like to continue with
my learning, but am stumped by this.
 
Could it have something to do with the focus on Repinfo tab?

What if you use the setfocus method to focus on another object on th
tab just prior to set namerange.... line
 
Back
Top