Option Button with VB code, not working

[pgarcia]

Why dosn't this work? Thanks
Sheets("INPUT_A").Select

If OptionButton2.Value = True Then
Sheets("AR").Select
Rows("17:20,35:38,53:56,78:81,102:105,140:147,160:167,181:187").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ElseIf OptionButton3.Value = True Then
Sheets("AR").Select
Range("7:8,25:26,43:44,68:69,92:93,136:137,156:157,176:177").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ElseIf OptionButton4.Value = True Then
Sheets("AR").Select
Range( _

"7:8,17:20,25:26,35:38,43:44,53:56,68:69,78:81,92:93,102:105,136:137,140:147,160:163,164:167,176:177,180:187,156:157" _
).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End If

Sheets("AR").Select
Range("A1").Select

or this:
If OptionButton2 Then
Call AR_GTD
ElseIf OptionButton3 Then
Call AR_Both
ElseIf OptionButton4 Then
Call AR_ECO
End If

 

[FSt1]

hi
what's not working?
you do understand that control code is sheet code. if you want something to
happen
another sheet, then you have to reference that sheet. otherwise, the code
assumes
the default sheet the code is assigned to.
instead of
rows().select
should be....
sheet("AR").rows().delete

am i close???
post back with more info.
Regards
FST1

 

[Susan]

you've got a couple of problems going on here. first is your
optionbutton value. if you are using a forms button in a spreadsheet,
below is the proper coding. the second thing is your deletion of rows
- you can't select or delete non-contiguous ranges/rows. you'll have
to do them one at a time. which will be hard to code because after
you delete the first bunch, the second bunch moves up four rows.

this works, but doesn't delete the second batch of rows you wanted,
because they've moved up 4 rows, as i mentioned.
==========================
Option Explicit

Sub garcia()

Sheets("Sheet1").Select

If Worksheets("Sheet1").OptionButtons("Option Button 2") _
.Enabled = True Then
Sheets("AR").Select
Range("a17:a20").EntireRow.Delete
Range("a35:a38").EntireRow.Delete
Range("A1").Select
End If

End Sub
=============================
but it might get you started.

as for these:
If OptionButton2 Then

if what? if it explodes? if it leaps off the computer screen? if it
turns yellow and starts speaking german? plus you will have to
identify it better, as in the sub above.
hope it helps!
susan

 

[pgarcia]

Will, it stops at "If OptionButton2.Value = True Then" and it will not
continue from there. This is the full code.

Sub Copy_Paste_AR()
'
Application.ScreenUpdating = False

Sheets("AR").Visible = True

Dim MyPath As String
Dim MyFileName As String

Sheets("AR").Select
Range("H1:H187", Range("H1:H187").End(xlDown)).Copy
Range("H1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Columns("A:G").Delete Shift:=xlToLeft

Sheets("INPUT_A").Select
If OptionButton2 Then
Call AR_GTD
ElseIf OptionButton3 Then
Call AR_Both
ElseIf OptionButton4 Then
Call AR_ECO
End If

Sheets("AR").Select
Range("A1").Select

MyPath = "S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\"
MyFileName = "d" & Sheets("INPUT_A").Range("C8").Value & "ar"

ActiveWorkbook.SaveAs Filename:=MyPath & MyFileName, _
FileFormat:=xlText, CreateBackup:=False

Sheets("INPUT_A").Select
MsgBox ("AR file has been created at:" & vbLf &
"S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\")

End Sub

 

[Jim Thomlinson]

As a guess we have an object heirarchy issue here. Options buttons from teh
control toolbox are embedded in the worksheet. That means that the worksheet
is the parent. If I write code in the worksheet the default object is the
worksheet so I do not explicitly need to reference thw worksheet to access
the button object. If however I want to access the button from another sheet
or a standard code module then I need to explicitly reference the worksheet.
For example If I embed an option button in sheet 1 then code written directly
in sheet 1 can be written as
sheet1.optionbutton1.value
or
me.optionbutton1.value
or
optionbutton1.value

To access the button from sheet 2 or a standard code module I can only use
sheet1.optionbutton1.value
where I have explicitly referenced the sheet object.

To fix your code in the VBE determine which sheet object holds the embedded
buttons. In the project explorer you will see the sheet listed something like
Sheet1(Input A)
so Sheet1 is the sheet object.
if you type in Sheet1 in the vbe when you hit the dot an intellisence list
will pop up and OptionButton1 will be in that list.

 

[Susan]

try this first & see if it gets past that first line. change the
worksheet name to the proper name.
====================
If Worksheets("Sheet1").OptionButtons("Option Button 2") _
.Enabled = True Then
===================
if you're using a forms optionbutton, it doesn't have a true value, it
has an enabled value.
susan

 

[pgarcia]

Thanks, but it seems it didn't work for me. Ah, I'm not sure whats happing,
it stops at:
If Worksheets("INPUT_A").OptionButtons("Option Button 2") _
.Enabled = True Then

Sorry, I'm just a novis and I have yet to pick up a book. The code was also
given to me, but by know, I know enoght that there should have been some
thing after the OptionButton2 (OptionButton2.value = true). But it just stop
running when it hits that spot.
Thanks

 

[FSt1]

hi
i thinks that is because you are trying to delete non consecutive rows. no
can do.
you can delete groups of rows but not non consecutive.

regards
FSt1

 

[Jim Thomlinson]

What makes you say you can not delete non contiguious ranges... This works
just fine as a test...

Sub test()
Range("3:5, 7:10").Delete
End Sub

 

[Jim Thomlinson]

The enabled property has nothing to do with the value of the button. The
value of the button is either 1 for checked or -4146 for unchecked. You can
toggle the enabled property of the button in code which will enable or
disable the button. If the button is disabled then it can not be selected or
deselected.

 

[Jim Thomlinson]

There is no issue with deleteing non-consecutive rows... This works just
fine...

Sub test()
Range("3:5, 7:10").Delete
End Sub

 

[Susan]

3 strikes & i'm out.................

susan

There is no issue with deleteing non-consecutive rows... This works just
fine...

Sub test()
Range("3:5, 7:10").Delete
End Sub
--
HTH...

Jim Thomlinson





- Show quoted text -

 

[pgarcia]

Ok, I did that and it still stops. I tried just about everyone’s suggestion
and it does not see to work. I guess I need a work around.
Can I email you the spread sheet?

 

[pgarcia]

Ah, I wrote, sheet1., but did not get Options buttons. I will see if I can
work around this.
Can I email you what I have?

 

[Jim Thomlinson]

Send away...
--
HTH...

Jim Thomlinson

Ah, I wrote, sheet1., but did not get Options buttons. I will see if I can
work around this.
Can I email you what I have?

 

[Jim Thomlinson]

You are only at 2 strikes and you have learned 2 things. Not a bad day in my
books...

 

[pgarcia]

Thanks Jim, but I did a work around. I created a valuation drop down list and
that worked out great. Still...