Refreshing Object Properties after "Set"

[Ken McLennan]

G'day there One and All,

I'm still plugging away at my application, but have hit another
hurdle. I'm sure that there's a way around it, but I can't find one that
won't require a huge rewrite of code while still providing the functions
that I've got so far (although that might still be necessary)

I have a userform with a multipage control. Each tab relates to a
different range, and has similar controls on it - a listbox, a textbox,
and "Add" & "Remove" command buttons. The data from the ranges is used
elsewhere as .rowsources for comboboxes. The user can then select from
their own lists that they previously used these multipage tabs to enter.
There are only 3 pages at present - Holidays, Sections, Ranks, but there
might be more later after I discuss further requirements. (Why is it
that you can't be given a set of UNCHANGING criteria to work with? <g>).

Anyhoooo... I'm using a "Set" statement on selection of multipage
tab:

Select Case Me.MultiPage1.Value
Case Is = 0 ' Defaults
Me.cmbSave.Visible = True
Me.cmbCancel.Left = 270
Case Is = 1 ' Location
Me.cmbSave.Visible = False
Me.cmbCancel.Left = (Me.Width / 2) - Me.cmbCancel.Width / 2
Case Is = 2 ' Ranks
Me.cmbSave.Visible = False
Me.cmbCancel.Left = (Me.Width / 2) - Me.cmbCancel.Width / 2
Set gRng = dSht.Range("wrkRanks")
Call modFormSet.setFrmLst(Me.lbRanks, gRng)
Case Is = 3 ' Sections
Me.cmbSave.Visible = False
Me.cmbCancel.Left = (Me.Width / 2) - Me.cmbCancel.Width / 2
Set gRng = dSht.Range("wrkSections")
Call modFormSet.setFrmLst(Me.lbSections, gRng)
Case Is = 4 ' Holidays
Me.cmbSave.Visible = False
Set gRng = dSht.Range("wrkHolidays")
Call modFormSet.setFrmLst(Me.lbHols, gRng)
End Select

I then use the listbox and range details in various reuseable Subs
in a general code module. Eg:

Sub lstRemBtn(lb As MSForms.ListBox, rng As Range)
brilliantly imaginative code goes here
End Sub

This Sub deletes 1 and only 1 entry from my range and updates the
Listbox to reflect this. The problem is that it can eventually remove
all entries from the list, and the next time the tab is selected an
error arrises because there's no range to select. I should point out
that these "wrkWhatever" ranges are dynamic named ranges.

Also, the listbox entries are entered via an array with .rowsource
set to "". This is because I also have "MoveUp" & "MoveDown" buttons
(from one of John Walkenbach's books), to allow my users to set their
own order for the range entries, and hence the later combobox selections
or cell entry validation.

The easiest way I thought of is to make the last range entry
undeletable. I.e. range("wrkWhatever").rows.count can never be < 1, but
the methods I've tried don't work. Once the range is "Set", and passed
to my Sub, the exposed properties don't change so gRng.Rows.Count always
gives the number of rows at the time of initialization. The same with
ListBox.ListCount.

Is there a way to update these properties once initialised?

Or can anybody see some other way around this issue?

I'll take any advice I can get. (Make that "polite" advice <g>).

See ya,
Thanks for listening

 

[Tom Ogilvy]

Set gRng = Nothing
On error Resume Next
Set gRng = dSht.Range("wrkRanks")
On Error goto 0


Then when using gRng

if not gRng is nothing then

- code to use gRng
End if

gRnd doesn't refer to the named range wrkRanks. It refers to the range
defined by wrkRanks when you set the variable. The only way to refresh that
range as you call it is to set it again or instead of using a variable, use
dSht.Range("wrkRanks") directly.

 

[Ken McLennan]

G'day there Tom,

Set gRng = Nothing
On error Resume Next
Set gRng = dSht.Range("wrkRanks")
On Error goto 0

Of course!! That makes sense. I've used the "on error" construct
elsewhere, but I thought that using it here would just translate the
same error to another location.

Then when using gRng

if not gRng is nothing then

- code to use gRng
End if

I had used the "if not" line with the above "Set" command, but
without success. This way looks much more logical.

gRnd doesn't refer to the named range wrkRanks.

Well, there was my first mistake =)

It refers to the range
defined by wrkRanks when you set the variable.

That was my second, and was what I learned from those mistakes. So
it wasn't a totally wasted experience.

The only way to refresh that
range as you call it is to set it again or instead of using a variable, use
dSht.Range("wrkRanks") directly.

OK, I can see the mechanism behind it all. Unfortunately,
"Set"ting it again or using the direct approach introduces other
problems that I'd rather not try to deal with at the moment. (Translate
that to mean, "I've not figured out how to do it". I'll stick with your
"On error" & "if not" techniques and exit the subs as necessary.

Thanks very much for your assistance, and explanation, Tom. I
really do appreciate the effort you put in to helping me learn.

See ya