Replies

I have attached a small 15kb excel file to demonstrate.
I have two ranges assigned in the demo: on separate sheets.
If you edit any of the entries that are ‘matches’ – i.e. change the last digit so that it no longer matches, you’ll see an instant update to the background.
This technique is great for ‘spotting errors’

zeddy

Have you tried ‘wrapping’ the chart legend onto more than one line?
Right-click on the chart legend and then grab and drag the handles to resize the box.
Then you might try changing the size of the pie itself – right-click on it and drag the handles.

zeddy

When I do a ‘reconciliation’ between two lists I like to use conditional formatting.
If I have a ListA and a ListB, I like to highlight all the items in List A which are NOT in List B with a coloured background e.g. red.
If neccessary, I then use conditional format formulas to do the same thing in List B, i.e. in List B, change the background for those items which are NOT in List A.
Then all of the items which have’normal’ backgrounds have corresponding entries in both lists.

This is straightforward if you are checking simple items e.g. a list of account numbers in List A against a master list in List B – then the highlighted cells in List A represent ‘new’ account numbers (or ‘unrecognized’ account numbers)

zeddy

I think your problem might be related to the bit
“The user’s selection is then reported to another cell which is then used in formula to make some decision”
I seem to recall a known Excel problem with listbox linked cells being subsequently referenced by other cells which use ‘volatile’ formulas. (see knowledgebase Q211784)
For example, if you use a cell to store the current listbox selection index (e.g 7 ) and then use that cell reference directly in a volatile formula elsewhere.
To get around this problem when using the listbox controls on Forms, I make sure that there are no cells on the sheet that are directly dependant on the listbox ControlSource. I use VBA to ‘paste’ a copy of the listbox index value into another cell and then have my formula cells use THAT cell as the reference.

Hope this helps.

zeddy

What kind of coffee is in the cups.

zeddy

Yes, but using vbModeless means you don’t need ANY code in the Form and you can turn it on and off whenever you like through simple code.
You can also use this method to display large message indicators during a long process of multiple steps, i.e. slap bang in the middle of the screen in whacking big easy to read letters (the bottom status bar can be a little small to notice for some Users. You simply set a label on the Form as each step commences and unload at the last step.

zeddy

With Excel 2000 you can use ‘modeless’ as well as ‘modal’ Forms, so for a splash screen use the vbModeless parameter. So in your workbook open routine or auto_open, you use
…
MySplashForm.Show vbModeless
Application.Wait xxxxx
MySplashForm.Unload

zeddy

zeddy

How come you have time for all that!

zeddy

Legare, I think my brain went dead – should’nt we just use the Application.Wait for five seconds???

zeddy

I like your answer better!!!

Thanks for the inside info!

zeddy

Looks like a bit of matrix math to me.

If you goto an empty cell, say [k1] type
=1.5*a1:f6
and then press Ctrl-Shift-Enter
You’ll see a single result.
Now select the cell with this result.
Press [F2] for Edit and then immediately press
[F9] – look in the top panel and you’ll see an array of all your values multiplied by your factor
Any use???

zeddy

Wow! that looks interesting!
I shall save this for later use.

Thanks
zeddy

To get at the AutoFilter criteria, turn on the macro recorder and then change some of the AutoFilter criteria.
Stop recording and then look at the code generated.
To simplify tasks, you could assign range names to the relevant cells.
You should then be able to set and retrieve the criteria through VBA

zeddy

If you assign a dynamic range name to the visible cells in the table you could use this range name as the RowSource for your Form listbox.

zeddy

Use the xxxxxxx.Show command to display the form.
Follow by Application.OnTime now plus 5 seconds
then xxxxxxx.Unload

where xxxxxx is the name of your UserForm
Do you want the exact code or is this enough???

zeddy