Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error
Hi, in my opinion, you have not defined Me !!!! Please define/declare and
initialise Me and then try to use its Controls method. " wrote: 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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error
On Apr 2, 12:00 pm, Dave Peterson wrote:
I assumed that you put a commandbutton from the Control toolbox toolbar on the worksheet. Did I assume correctly? wrote: 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!! -- Dave Peterson- Hide quoted text - - Show quoted text - Yes I did. Is that part of my problem |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error
On Apr 2, 12:00 pm, Dave Peterson wrote:
I assumed that you put a commandbutton from the Control toolbox toolbar on the worksheet. Did I assume correctly? wrote: 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!! -- Dave Peterson- Hide quoted text - - Show quoted text - I tried your method and now it gives me a type mismatch error. Any thoughts??? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error
On Apr 2, 1:04 pm, Dave Peterson wrote:
Yep. Go through the OLEObjects collection. wrote: On Apr 2, 12:00 pm, Dave Peterson wrote: I assumed that you put a commandbutton from the Control toolbox toolbar on the worksheet. Did I assume correctly? wrote: 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!! -- Dave Peterson- Hide quoted text - - Show quoted text - Yes I did. Is that part of my problem -- 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???? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error
On Apr 2, 1:57 pm, Dave Peterson wrote:
Share what you tried and share the line that caused the trouble. wrote: On Apr 2, 1:04 pm, Dave Peterson wrote: Yep. Go through the OLEObjects collection. wrote: On Apr 2, 12:00 pm, Dave Peterson wrote: I assumed that you put a commandbutton from the Control toolbox toolbar on the worksheet. Did I assume correctly? wrote: 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!! -- Dave Peterson- Hide quoted text - - Show quoted text - Yes I did. Is that part of my problem -- 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- 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)" |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error
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 wrote: 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)" -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with this error-Compile error: cant find project or library | Excel Discussion (Misc queries) | |||
VBAProject name compile error, not defined at compile time | Excel Programming | |||
error message: compile error, argument not optional | Excel Programming | |||
How do I get rid of "Compile error in hidden module" error message | Excel Discussion (Misc queries) |