ActiveX Combo Box fires when Worksheet Activate Fires

[kalecomm]

This is all done in Excel 2000

I keep getting this error: "Unable to get the find property of the range class".

I have one workbook with 3 sheets on it. I have 3 ActiveX ComboBoxes, one of which is dependent on the other.

Sheet 1 is my "form" where the ActiveX ComboBoxes are.
Sheet 2 is called "Data" and it's where I pull data from and copy to Sheet3 to populate the ComboBoxes. (Only way I knew how to do this. I'm new to VBA).

Column A looks like this:
(List of Builders)
Centex
Centex
David Weekley
David Weekley
David Weekley
David Weekley
David Weekley
David Weekley
David Weekley
David Weekley
David Weekley
David Weekley
David Weekley
David Weekley
David Weekley
David Weekley
Keith Builders
Lennar
Lennar
Lennar
Lennar
Lennar
Lennar
Lennar
Lennar
Lennar
Lennar
Lennar
Morrison
Morrison
Morrison
Morrison

Column B looks like this:
(List of Subdivisions)

Trailside
Westview Meadows
Canyon Creek
Georgetown Village
Great Hills
Heritage Park
Loma Vista
Marble Falls
Oakland Village
Pioneer Crossing
Ridgewood S/R
Riverplace
Senna Hills
Shady Hollow
Steiner Westridge
Stone Canyon
Fubar
Amberwood
Chandler Creek
Enclave at Brushy Creek
Forest Oaks
Georgetown Village
Greenbury Estates
Hutto Park
Legends of Hutto
Prairie on the Creek
Summer Crest
Wild Horse Ranch
Deer Creek Ranch
Rio Mesa
Steiner Westridge
Wood Glen

I have a button on this sheet that puts a unique list of builders to Sheet3:ColumnA. That code looks like this:
Sub Button1_Click()
Worksheets("Data&quot.Range("A2:B1000&quot.Sort _
Key1:=Worksheets("Data&quot.Range("A2&quot, _
Key2:=Worksheets("Data&quot.Range("B2&quot, _
Order2:=xlAscending

Worksheets("Data&quot.Range("C2:C1000&quot.Sort _
Key1:=Worksheets("Data&quot.Range("C2&quot

Worksheets("Data&quot.Range("D21000&quot.Sort _
Key1:=Worksheets("Data&quot.Range("D2&quot

Dim UniqueBuilders As Range
Set UniqueBuilders = Range("Data!A2:A1000&quot
Set CopyToRange = Range("Sheet3!A1&quot
UniqueBuilders.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=(CopyToRange), Unique:=True

End Sub


This code is also referenced in the Workbook_SheetActivate. Code looks like this: (ignore the comments, I'm trying a few things to get this work consistently).

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Module1.Button1_Click
End Sub

Sheet 3 is where the ranges (columns) are used to populate the 3 ComboBoxes.

Here's the code that populates ComboBox2 based on the selection by the user of ComboBox1:

Private Sub ComboBox1_Change()
Dim dd_choice As String
Dim firstAddress, lastAddress, temp1, temp2, temp3, temprng As String

dd_choice = Sheets("Sheet1&quot.ComboBox1.Value

'Sheets(Datasheet).Columns(1).Find(What:=FirstComp, LookIn:=xlValues).Activate
' firstrow = ActiveCell.Row + 1

With Sheets("Data&quot.Range("A2:A71&quot
Set c = .Find(What:=dd_choice, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
lastAddress = c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

'With Sheets(&quot;Data&quot.Columns(1)
' .Activate
' Set c = .Find(What:=dd_choice, LookIn:=xlValues)
' If Not c Is Nothing Then
' firstAddress = c.Address
' Do
' lastAddress = c.Address
' Set c = .FindNext(c)
' Loop While Not c Is Nothing And c.Address <> firstAddress
' End If
'End With

temp1 = &quot;B&quot; & Right(firstAddress, Len(firstAddress) - 3)
temp2 = &quot;B&quot; & Right(lastAddress, Len(lastAddress) - 3)
temp3 = temp1 & &quot;:&quot; & temp2

Worksheets(&quot;Sheet3&quot.Range(&quot;b1:b1000&quot.Value = &quot;&quot;

If temp1 = temp2 Then
Worksheets(&quot;Sheet3&quot.Range(&quot;B1&quot.Value = Worksheets(&quot;Data&quot.Range(temp1).Value
Else
temprng = Trim(Str(Val(Right(lastAddress, Len(lastAddress) - 3)) - Val(Right(firstAddress, Len(firstAddress) - 3)) + 1))
Worksheets(&quot;Sheet3&quot.Range(&quot;B1:B&quot; & temprng).Value = Worksheets(&quot;Data&quot.Range(temp3).Value
End If

End Sub


Now, this code, ComboBox1_Change(), works, but it fires whenever Workbook_SheetActivate(ByVal Sh As Object) fires. I don't understand why. This is where I get the &quot;Unable to get the find property of the range class&quot; error. When ComboBox1_Change() is moved to it's own module (like Module2, it's currently on Sheet1(Sheet1)), It doesn't fire when you change ComboBox1.

If anyone could help me out with this, I'd sure appreciate it!

Thanks in advance!

Best Regards,

Kalecomm.

 

[kalecomm]

OK. I've figured out that the Change Method of ComboBox1 isn't being reset to False, and that is why It's firing when the Activate Method of the Sheet fires. Anyone know why this would be?

Best Regards,

Kalecomm

 

[SkipVought]

Hi,

Why do you have...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Module1.Button1_Click
End Sub
[code]
Not a good practice.  If you have a Procedure (the sorts)that Button1_Click runs and you also want that to run when the sheet is activated, then both ought to run the procedure.  The sort Procedure needs to be in a Module.  That's probably NOT the reason that the combobox_Change is firing.  

Sheet control events can only be in a sheet object, not a module.  Button1_Click needs to be in it's sheet object NOT in a module.  Seems like you may have some code screwed up!


Skip,
Skip@TheOfficeExperts.com
[URL unfurl="true"]www.TheOfficeExperts.com[/URL]

 

[kalecomm]

I'm new to VBA and OOP, so this does not surprise me. I come from a FoxPro 2.6 background, so a lot of this doesn't make sense to me...and believe me, I'm trying here!

I need the sort procedure to fire whenever a worksheet is changed (people here forget to do stuff like click the sort button, so it would help if it is done for them.) and I need to have the change event of combobox1 fire whenever the user changes one of the Builders, so that it will populate the Subdivisions with the appropriate list of subdivisions associated with the builder.

As far as why the:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Module1.Button1_Click
End Sub
I put that there so that the sort procedure would fire whenever the sheet was changed (From Data to Sheet1). It's a safeguard in case the user forgets to click the sort button. It's also &quot;reusable&quot; code which I thought was OOP's strength.

As far as why the Button1_Click is in a module, when you right click on the Sheet2, it offers up 3 choices: Insert: Userform, Module, or Class Module. I needed a place to put code, so I assumed that the Module would be the place (best guess). If that's not the place, where should I put it?

Lastly, but not least, I want to thank you for reading through that long post and helping me out with the code on this. It's really tough when the boss says &quot;Do it in Excel&quot; and you realize that you know NOTHING about OOP or VBA and had better try to at least figure it out as soon as you can!

If you could guide me a little more, I'd sure appreciate it!

Thanks!

Best Regards,

Kalecomm

 

[SkipVought]

When you have code recorded in a sheet, workbook, chart or userform object, you are not able to reference objects outside of that object. Thats when to use a Module.

If you need to trap changes on a sheet, then use the Worksheet_Change event if the process is sheet specific or the Workbook_SheetChange event if several or all worksheets are affected by the same process.

What you need to be careful of is a recursive process happening. If, for instance, a change in one cell triggers a change in that cell of another cell, then Worksheet_Change will fire again.

Hope this helps. Post back with any other questions.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com