Use optionbutton value to select code to run.

C

Casey

Hi,
I have sheet that imports data from another sheet via a comand button
The sheet also has two option buttons. What I am attempting to do i
import slighty different information from the other sheet depending o
which optionbutton is selected. (There is code behind the optio
buttons to insure only one can be chosen at a time.)
I am getting the following error message:

Compile Error:
Method or Data Member not found.

This is a new error message for me and nothing in Help is given me an
more ideas to try.

Here is my Code:

Option Explicit
Sub Import_CWR_SUBCON_COSTS_To_SubConCO()

Dim Wks1 As Worksheet
Dim Wks3 As Worksheet
Dim CopyRow As Long
Dim Entries As Long
Dim i As Long
Dim N As Long
Dim Cnt As Integer
Dim Msg As Integer
Dim Response As Integer
Dim OptionButton1 As Object '<<<<<<<<< Not sure this is correct
Dim OptionButton2 As Object '<<<<<<<<< Not sure this is correct
Dim Opt1 As Boolean
Dim Opt2 As Boolean

Msg = MsgBox("Have you double checked the SUB CO NO: and that the sam
SUB CO NO:" _
& " has been entered in the appropriate cell of the CWR Log for each
_
& " CWR you want to make part of this Subcontractor Change Order ?", _
vbYesNo + vbQuestion, "Import CWRs to Subcontractor Change Order by SU
CO NO:")
If Msg = 6 Then
Application.ScreenUpdating = False

Set Wks1 = Worksheets("CWR LOG")
Set Wks3 = ActiveSheet
Opt1 = Wks3.OptionButton1.Value '<<<<<<<Compile Error
Opt2 = Wks3.OptionButton2.Value '<<<<<<<Compile Error
Wks3.Range("SubCon_Entry_Range").ClearContents
CopyRow = 30
Entries = Excel.WorksheetFunction.CountA(Wks1.Range("B:B"))
Cnt = 0
For i = 9 To Entries + 100
N = Wks1.Cells(i, 2).Value
If N = Range("SubCon_CHANGE_ORDER_NO") Then Cnt = Cnt + 1
If Cnt > 15 Then
Msg = MsgBox("You are attempting to import more than 15 CW
records. Only the First 15 Records will be pasted. Please return to th
CWR Log Page and reduce the number of CWRs you want to make part o
this Change Order to 15 or less. Then hit the Import Subcontracto
Costs from CWR's button again.", vbOKOnly + vbCritical, "Exceede
number of Records")
If Response = 1 Or 2 Then
Exit Sub
End If
ElseIf N = Range("SubCon_CHANGE_ORDER_NO") And Cnt <= 15 _
And Opt1 = True Then
With Wks3
.Unprotect ("geekk")
.Cells(CopyRow, 2).Value = Wks1.Cells(i, 3).Value
.Cells(CopyRow, 3).Value = Wks1.Cells(i, 1).Value
.Cells(CopyRow, 4).Value = Wks1.Cells(i, 4).Value
.Cells(CopyRow, 5).Value = Wks1.Cells(i, 6).Value
.Protect ("geekk")
End With
CopyRow = CopyRow + 1
ElseIf N = Range("SubCon_CHANGE_ORDER_NO") And Cnt <= 15 _
And Opt2 = True Then
With Wks3
.Unprotect ("geekk")
.Cells(CopyRow, 2).Value = Wks1.Cells(i, 3).Value
.Cells(CopyRow, 3).Value = Wks1.Cells(i, 1).Value
.Cells(CopyRow, 4).Value = Wks1.Cells(i, 4).Value
.Cells(CopyRow, 5).Value = Wks1.Cells(i, 7).Value
.Protect ("geekk")
End With
CopyRow = CopyRow + 1
End If
Next i

Wks3.Range("L5").Activate
Application.ScreenUpdating = True
End If
If Msg = 7 Then
Exit Sub

End If
End Su
 
B

Bob Phillips

Maybe try

Opt1= Wks.OLEObjects("OptionButton1").Object.Value


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
J

Jim Thomlinson

The option buttons are embedded in the sheet I presume. My question is what
kind of buttons are they? Are they from the control toolbox or are they from
the forms toolbar? If they are from the control toolbox then you can read the
values very sismiar to what you have. You just need to delete the dim
statements for the option buttons.

Try this to see what I mean. In a new workbook on sheet1 add two option
buttons from the control toolbar (don't rename the buttons). Select one of
the option buttons. In the VBE add a standard code module and add the
following code to it

Sub test()
MsgBox Sheet1.OptionButton1.Value
MsgBox Sheet1.OptionButton2.Value
End Sub
 
C

Casey

Jim and Bob,
Thanks for the replies.
Jim you surmised correctly, the optionbuttons are embedded on the sheet
and they are from the control toolbox. Sorry, I should have included
that in my OP. I tried your exercise and the msgboxes worked as
advertised, however when applying the method to my routine I am still
getting the same error.
Bob I tried your suggestion and got a different error message:
Run-time error 5
Invalid proceedure caller arguement. again a new error for me.

Here is my code with the Dim statements and Bob's suggestions commented
out.

Sub Import_CWR_SUBCON_COSTS_To_SubConCO()

Dim Wks1 As Worksheet
Dim Wks3 As Worksheet
Dim CopyRow As Long
Dim Entries As Long
Dim i As Long
Dim N As Long
Dim Cnt As Integer
Dim Msg As Integer
Dim Response As Integer
'Dim OptionButton1 As Object '<<<<<<<<< Not sure this is correct
'Dim OptionButton2 As Object '<<<<<<<<< Not sure this is correct
Dim Opt1 As Boolean
Dim Opt2 As Boolean

Msg = MsgBox("Have you double checked the SUB CO NO: and that the same
SUB CO NO:" _
& " has been entered in the appropriate cell of the CWR Log for each"
_
& " CWR you want to make part of this Subcontractor Change Order ?", _
vbYesNo + vbQuestion, "Import CWRs to Subcontractor Change Order by SUB
CO NO:")
If Msg = 6 Then
Application.ScreenUpdating = False

Set Wks1 = Worksheets("CWR LOG")
Set Wks3 = ActiveSheet
Opt1 = Wks3.OptionButton1.Value '<<<<<<<Compile Error
Opt2 = Wks3.OptionButton2.Value '<<<<<<<Compile Error
'Opt1 = Wks3.OLEObjects(OptionButton1).Object.Value '<<<<<<<Run-time
error 5
'Opt2 = Wks3.OLEObjects(OptionButton2).Object.Value '<<<<<<<Run-time
error 5
Wks3.Range("SubCon_Entry_Range").ClearContents
CopyRow = 30
Entries = Excel.WorksheetFunction.CountA(Wks1.Range("B:B"))
Cnt = 0
For i = 9 To Entries + 100
N = Wks1.Cells(i, 2).Value
If N = Range("SubCon_CHANGE_ORDER_NO") Then Cnt = Cnt + 1
If Cnt > 15 Then
Msg = MsgBox("You are attempting to import more than 15 CWR
records. Only the First 15 Records will be pasted. Please return to the
CWR Log Page and reduce the number of CWRs you want to make part of
this Change Order to 15 or less. Then hit the Import Subcontractor
Costs from CWR's button again.", vbOKOnly + vbCritical, "Exceeded
number of Records")
If Response = 1 Or 2 Then
Exit Sub
End If
ElseIf N = Range("SubCon_CHANGE_ORDER_NO") And Cnt <= 15 _
And Opt1 = 1 Then
With Wks3
..Unprotect ("geekk")
..Cells(CopyRow, 2).Value = Wks1.Cells(i, 3).Value
..Cells(CopyRow, 3).Value = Wks1.Cells(i, 1).Value
..Cells(CopyRow, 4).Value = Wks1.Cells(i, 4).Value
..Cells(CopyRow, 5).Value = Wks1.Cells(i, 6).Value
..Protect ("geekk")
End With
CopyRow = CopyRow + 1
ElseIf N = Range("SubCon_CHANGE_ORDER_NO") And Cnt <= 15 _
And Opt2 = 1 Then
With Wks3
..Unprotect ("geekk")
..Cells(CopyRow, 2).Value = Wks1.Cells(i, 3).Value
..Cells(CopyRow, 3).Value = Wks1.Cells(i, 1).Value
..Cells(CopyRow, 4).Value = Wks1.Cells(i, 4).Value
..Cells(CopyRow, 5).Value = Wks1.Cells(i, 7).Value
..Protect ("geekk")
End With
CopyRow = CopyRow + 1
End If
Next i

Wks3.Range("L5").Activate
Application.ScreenUpdating = True
End If
If Msg = 7 Then
Exit Sub

End If
End Sub
 
C

Casey

Bob,
I apologize, looking again at you code I see I neglected to include th
"" for the OptionButton names. I retried your code after correcting tha
oversight and it works beautifully. Many thanks for the help
 
B

Bob Phillips

I guessed correctly than it was control toolbox <G>

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 

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

Similar Threads

InputBox for column letter problem 2
Getting a count of formula results only. 2
first sip of working with range in vba excel 6
Compile error: End If without block IF 1
424 error on a Worksheet Change Macro 2
Run-time error 1004 on variable 6
Find the correct sheet then find a value on that sheet 8
copy multiple rows 9

Top