Compile error

S

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("C98:D98")
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!!
 
M

Madhan

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

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
 
D

Dave Peterson

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

Did I assume correctly?
 
S

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
 
S

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???
 
S

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????
 
S

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("C98:D98")
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)"
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top