Switching Forms

[Daxton A.]

Hello,
I have a dictionary that has to be called using a button on a spreadsheet. The first form pops up and it is the given categories that the dictionary currently has available. After a category in the listbox is selected, the form gets hidden and the variables form shows.

Going into the dictionary the first time does not give me a problem. The problem occurs when I want to go back to the categories form and select another category. They all pop up but nothing happens when I select a category.

Flow of forms of the dictionary:

1. Categories ("A, B") > 2. Variables ("Active, Active Content")

[johnske]

Your problem exists because you're only 'hiding' the userforms. The UserForm_Activate procedure isn't called the second time because they're not being re-activated. It'll work when you replace the 'Hide' commands on both userforms with: Unload Me

[johnske]

BTW, the first form will load much faster if you use the find function. E.G.

[vba]Private Sub UserForm_Activate()
'
Dim Cell As Range, FirstAddress As String
'
With Range("A2", Range("A" & Rows.Count).End(xlUp).Address)
'
Set Cell = .Find("*", After:=Range("A" & Rows.Count).End(xlUp), LookIn:=xlValues)
'
If Not Cell Is Nothing Then
'
FirstAddress = Cell.Address
'
Do
lstCategories.AddItem Cell
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
'
End If
'
End With
DoEvents
'
End Sub[/vba]

[Daxton A.]

In the code:
[VBA]Set cell = .Find("*", After:=Range("A" & Rows.Count).End(xlUp), LookIn:=xlValues)[/VBA]

This is what I know:
.Find(ANYTHING, Starting ?, Not Sure)

Can I get an explanation of the, not sure what you call it, the event?

[johnske]

The asterisk is the wildcard character (anything - yes).

The find function has a quirk in that the first thing found will be added to the list last, so we start the search at the last possible address that there is for anything to be found and the last thing then becomes the first thing that is found and is then duly added to the list last (if that makes sense to you)

[Daxton A.]

Well John, I got the first sentence and I am pretty sure that I understand what you typed. I just need explanations of what is meant with the [VBA]).End(xlUp), LookIn:=xlValues[/VBA] What are the .End and xlValues properties?

I will not reply for another day. I appreciate your class of skills and being so forth coming.

Daxton

[johnske]

As used, (Range("A" & Rows.Count).End(xlUp)) the End(xlUp) refers to the last value that's in column A (i.e. or, the first value above Range("A" & Rows.Count) - which means Range("A65536") in Office 2000 - but the number of rows can differ from version to version).

LookIn:=xlValues refers to all the Values in that column (as opposed to xlFormulas or xlComments, which are the other options in the find function {N.B. use xlFormulas as the argument when finding dates})