Compile error

[Spyntek]

Hello. I have the following code here and it is giving me problems

Private Sub CommandButton3_Click()
Dim i As Long, j As Long
Dim rng1 As Range, rng2 As Range
Dim rng3 As Range, rng3a As Range
Dim rng4 As Range, rng4a As Range


Set rng1 = Sheet3.Range("D91")
Set rng2 = Sheet1.Range("A98") ' "29 120"
Set rng3 = Sheet3.Range("B91")
Set rng3a = Sheet1.Range("C9898")
Set rng4 = Sheet3.Range("C91")
Set rng4a = Sheet1.Range("E98:F98")
For j = 1 To 4
Set cbox = Me.Controls("ComboBox" & j)
For i = LBound(Jobs) To UBound(Jobs)
If Jobs(i) = cbox.Value Then
rng1.Offset(i, 0) = Jobs(i)
rng2.Value = "29 120"
rng3.Offset(i, 0).Value = Application.Sum(rng3a)
rng4.Offset(i, 0).Value = Application.Sum(rng4a)
End If
Next i, j


When I try to compile it, it gives me the error message "Compile
Error" "Method or data member not Found," with "Private Sub
CommandButton3_Click()" and "Me.Controls" highlited.

This is a continuation from before with an array "Jobs" of about 22
members in this array already created in a previous Sub command.

I have looked all over and have found no help on this subject. I hope
someone else can help!!

 

[Guest]

Hi, in my opinion, you have not defined Me !!!! Please define/declare and
initialise Me and then try to use its Controls method.

 

[Dave Peterson]

Controls exist on a UserForm--not on the worksheet.

On the worksheet, you can go through the OLEObjects collection:

Dim cBox As OLEObject
dim j as long

'some more code...
Set cBox = Me.OLEObjects("ComboBox" & j)

'and check the .value with something like:
MsgBox cBox.Object.Value

 

[Dave Peterson]

I assumed that you put a commandbutton from the Control toolbox toolbar on the
worksheet.

Did I assume correctly?

 

[Spyntek]

I assumed that you put a commandbutton from the Control toolbox toolbar on the
worksheet.

Did I assume correctly?












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Yes I did. Is that part of my problem

 

[Spyntek]

I assumed that you put a commandbutton from the Control toolbox toolbar on the
worksheet.

Did I assume correctly?












--

Dave Peterson- Hide quoted text -

- Show quoted text -

I tried your method and now it gives me a type mismatch error. Any
thoughts???

 

[Dave Peterson]

Yep. Go through the OLEObjects collection.

Yes I did. Is that part of my problem

 

[Spyntek]

Yep. Go through the OLEObjects collection.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

I am sorry, I am relatively new to VB programming Once I use type in
OLEObjects, is there any further code needed to implement the
function????

 

[Dave Peterson]

Share what you tried and share the line that caused the trouble.

 

[Spyntek]

Share what you tried and share the line that caused the trouble.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Private Sub CommandButton3_Click()
Dim i As Long, j As Long
Dim rng1 As Range, rng2 As Range
Dim rng3 As Range, rng3a As Range
Dim rng4 As Range, rng4a As Range
Dim cbox As OLEObjects


Set rng1 = Sheet3.Range("D91")
Set rng2 = Sheet1.Range("A98") ' "29 120"
Set rng3 = Sheet3.Range("B91")
Set rng3a = Sheet1.Range("C9898")
Set rng4 = Sheet3.Range("C91")
Set rng4a = Sheet1.Range("E98:F98")
For j = 1 To 4
Set cbox = Me.OLEObjects("ComboBox" & j)
For i = LBound(Jobs) To UBound(Jobs)
If Jobs(i) = cbox.Value Then
rng1.Offset(i, 0) = Jobs(i)
rng2.Value = "29 120"
rng3.Offset(i, 0).Value = Application.Sum(rng3a)
rng4.Offset(i, 0).Value = Application.Sum(rng4a)
End If
Next i, j

The line that gave me problems was "Set cbox =
Me.OLEObjects("ComboBox" & j)"

 

[Dave Peterson]

This is what I suggested:

Dim cBox As OLEObject
dim j as long

'some more code...
Set cBox = Me.OLEObjects("ComboBox" & j)

'and check the .value with something like:
MsgBox cBox.Object.Value

Notice that I declared cBox as OLEObject--not OLEObjects (with that S)

And I used cbox.object.value--not cbox.value