Replies

There is a work around, IF you can use VBA. The following…

Range(“Database”).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=””, _
CopyToRange:=Range(“Extract”), Unique:=True

Will copy a unique list of cities to the Extract range, providing the sheet for this range is on is active at the time. I achieved this simply by creating a button on the same sheet as the Extract range then sticking the above to it’s click event. My advice is DO NOT assign a keyboard shortcut to it. That way the you have to have the Extract sheet active to click the button.

An advantage is you can have an Extract range on a sheet all on it’s own, so your extracted list can be as long as you like (up to Excel’s limit of rows). Placing the text “Cities” in cell A4 and naming it “Extract” will work. Just remember that all previous data below A4 will be wiped. I know it’s VBA but it’s a tip all the same for anyone else.
Ewan

Hey i thought that was simple VBA!!!
It sounds as though you need to look into EVENT procedures. This is not as scary as it sounds. Use the VB tool bar to activate the editor and then double click your sheet name. This will bring up the code window on the right of the screen. At the top of the window there are two drop down boxes. The left hand one will probably have “(General)” and the right one will say “(Declarations)”. Bring down the left hand list and select “Worksheet”, the right hand one will probably change to “Selection Change” and the bare bones of a Worksheet_SelectionChange sub-routine will appear, feel free to delete it. Bring down the right hand list and select “Calculation”, now a Worksheet_Calculation procedure is created. Inside you fill in the procedure to something like:

Private Sub Worksheet_Calculate()
Dim MyVariant AS Variant

MyVariant=RandList(4,4)

‘Your code goes here
‘for example
Worksheets(“Sheet1”).Range(“A1”).Value = MyVariant(1)
‘will place the first value in MyVariant into A1 on sheet1

End Sub

And don’t forget to paste in my RandList function above the procedure!! The procedure will be run everytime the sheet is recalculated and a different random order of 1234 will be generated. You don’t say what you want with the array generated e.g. to be stuck in a cell as an array formula or the individual values placed in different locations. As an afterthought i use office 97 but i don’t imagine it’s much different for 2000 (this where 2000 users make a of me!!).
Ewan

I’ve decided to use a check box. It’s more discrete than some big grey toggle button and doesn’t require playing around with user settings (which can upset some!!) or putting buttons on the document to specifically hide controls during printing/print certain sections. I am assuming there is no equivalent to a print range in Excel, where once you name it you can leave it alone.
Thanks for the input everyone,
Ewan
P.S. Could start a whole new thread on features in one office app that should be in the others!!

Hi bob,
I wrote the following function for an exam generator (see post on preventing controls printing). I needed to randomly select questions from a list, without ever repeating a question and to randomise the order the answers appear, else the students would gradually associate the position of an answer to a given question. To produce a list of unique numbers in the range 1 to 4 call the function with MyVariant=RandList(4,4) and then step through the array assigned to MyVariant.

Function RandList(ByVal MaxListValue As Long, Optional ByVal ListSize As Long) As Variant

‘Produces a list of unique numbers, in the range 1 to MaxlistValue
‘The size of the list being 1 to ListSize or MaxListValue, whichever is smaller
‘The list is stored in ListArray
Dim ListArray() As Long
Dim AvailableValues() As Long
Dim counter As Long
Dim ListArraySize
Dim RandNumber As Long

‘Initialise AvailableValues to array of 1 to MaxListValue and fill array with values 1 to MaxListValue
ReDim AvailableValues(1 To MaxListValue) As Long
For counter = 1 To MaxListValue
AvailableValues(counter) = counter
Next

‘Determine size of array to be returned by function
If ListSize > MaxListValue Then
ListArraySize = MaxListValue
Else
ListArraySize = ListSize
End If
ReDim ListArray(1 To ListArraySize) As Long

For counter = 1 To ListArraySize
‘Get a RandNumber between 1 and upper limit of AvailableValues
Randomize
RandNumber = Int(UBound(AvailableValues) * Rnd + 1)

ListArray(counter) = AvailableValues(RandNumber)

‘Swap last item with selected item
AvailableValues(RandNumber) = AvailableValues(UBound(AvailableValues))

‘Shrink size of AvailableValues by 1 to get rid of used number
If UBound(AvailableValues) > 1 Then
ReDim Preserve AvailableValues(1 To UBound(AvailableValues) – 1)
End If
Next

RandList = ListArray
End Function

The function has the advantage of taking the same time for a given size of list. This could be important as list sizes increase and the size of list approaches or equals that of the available values. The disadvantage is memory used up as list size increases but i doubt this would be a factor in your case. Hope it helps,
Ewan

Thanks for the replies. I think i need to explain a bit more…
I’m generating a multi-choice exam using an excel database of questions and responses. A VBA routine randomly selects a requested number of unique questions and scrambles the answers. A Word doc is used to hold the questions and answers, plus any graphic and the correct response. So the whole list of questions would be in the format:-

Some question here?
Response A goes here
Response B goes here
Response C goes here
Response D goes here
Correct reponse is: A – D

The questions are formated as Heading 1style, the choices as Heading 2 style and the correct response para (which also holds any inline graphic) as Heading 3 style. Headings 1 and 2 are set up to outline number and Heading 3 has no number but does have a bottom border. The whole thing works very well, with all questions and responses automatically numbering and lettering plus any graphics below the last reponse being exactly the same width as the column. To toggle between hiding and showing the correct answers i placed a toggle button at the top of the first page to switch the font colour of the Heading 3 style between white and auto.

I checked the FAQ and the text box technique is intresting (if a pain in the ass) but it assumes i want a button for printing. To get the sample code to work i would need to place a button on the document for printing. I even checked for event procedures like Before_print in excel, but no luck. All these things that are in one App but not another is enough to drive you !!

Hey Andrew
I can’t replicate the error, but good idea. I’ll alert the user to try it if it occurs again
Thanks for the help everyone
Ewan

Hello pieterse,
To my knowledge the user was stuck with looking at one sheet. The behaviour was as if someone had disabled sheet tabs from Tools>Options. Luckily it was a data entry sheet that was active, they just couldn’t click a tab to jump to a summary sheet.