User form bug

[SteveDB1]

I've created a user form.
I have one last "bug" that I need resolved.

For further explanation, when we place a worksheet function in a worksheet,
when a range, or cell is chosen, it's "highlighted" by a colored outline of a
box for that range of cells. If one chooses a series of individual cells,
there are multiple colored boxes.

This is what I was looking for, so that the user could keep clear what
they'd chosen while going through the user form.

Is there a means that I can have these colored outlines, on the worksheet
while I'm selecting ranges for each of my RefEdit elements? If so, how? Is
this located in the properties box of each RefEdit element?

I have looked through the properties of RefEdit, and found HideSelection. I
set it to false, and this does not resolve my issue.



Again-- thank you for your helps.
Best,
SteveB.

 

[JLGWhiz]

RefEdit is a built in facility. VBA does not routinely provide facilities to
modify (expand) the properties of built in functions and dialog boxes.

 

[SteveDB1]

Hi JLG,

Ok, no modifications to built-ins. Routinely speaking..... of course I
desire to "break" the routine here, so what about......

Is there a facility, or device that I can use for selecting ranges, that
does allow for the change that I'm referring to?

More than anything I want to just show cells/ranges having been selected, as
they show when one is using a worksheet function.

Thanks for your help.
Best,
SteveB.

 

[Bob Bridges]

Interesting idea. Does the user type a whole range spec into a text box?
What I'm wondering is whether something really simple would work:
Once you've established that your user has entered a valid range, you
can refer to the range as an object, like this:

Set oRng = YourWorksheet.Range(UserEntryBox)

....where UserEntryBox is, of course, the name of the text box into which
the user typed the range. That works only if the user actually types in
something like "A5:G23", but if so....

Once you have the range, save that range's Borders settings in a
Collection and then change the four Borders' line style and color to
whatever you like. Once you've highlighted all the ranges the user is
going to enter, and everything's copacetic and approved, your program
works its way backward through the collection to restore the borders to
what they were. I say "backward" because of the chance that the user
enter the same range more than once; you want to be sure you restore
its ORIGINAL borders. But you know best whether that's a possibility.

 

[SteveDB1]

Hi Bob,
The user selects the range desired by standard procedure-- the RefEdit box
allows for the selection of a range, or cell. The textbox required the user
to type the cell/range-- which was irritating/time-consuming/frustrating.

It sounds as though you're talking setting the std borders around a
selection once chosen. While indeed interesting, I do that already.
Unfortunately, they do not appear until AFTER the macro has run its course,
and is complete.

I'm talking about the colored "selection" box that occurs when the user has
a worksheet function such as sum, etc.... E.G., After the " =sum( " is typed,
a colored box appears to show the selection cell/range. For each subsequent
selection following a comma becomes a different color. These colored boxes
also occur on UDF's as part of the range selection.
As best as I can tell, it's a standard element of the range, cell selection
process when an "=worksheet function" is typed in to a cell. One would think
it'd be standard across the board, mainly because there's no discussion that
I can find on it.
In my case, I have multiple RefEdit boxes and only one selection range/cell
per RefEdit box.
Once the next RefEdit box is activated, the "selection" outlining ceases,
and you can only tell where you've selected by looking at the contents of the
RefEdit box previously selected.
While I've set ControlTipText 'notes' for each box on my form, I've realized
that the colored selection boxes used in normal worksheet functions are far
more helpful in keeping track of where I am in relation to previously
selected ranges/cells than are my tips.

The closest discussion that I've seen on these things is something called-
vbActiveBorder for VB. But the discussion is so short, and vague as to leave
more questions than answers, and I can't find any thing that will allow me to
state them within my code to find out whether they'll work for me or not.
None of the books we've bought define them, no discussions on the library for
MS beyond a single statement.

Thanks for your help. I do appreciate it.

 

[SteveDB1]

JLG,
I have a question for you about your statement on routinely not providing
facilities for what I've described.
I tested a UDF earlier and found that the colored selection boxes appear
there as well as on worksheet functions.

Since the UDF is a custom function, what is happening there that allows the
colored outline-selection boxes that we cannot obtain using a RefEdit?

My point being that I find it interesting that one can build a UDF, and
obtain the same results as a standard worksheet function, but in using a
RefEdit we cannot obtain the desired result.