| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button with VB code, not working
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
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 "pgarcia" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
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 On Jul 25, 2:14*pm, pgarcia wrote: 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:14*7,160:163,164:167,176:17 7,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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
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. -- HTH... Jim Thomlinson "pgarcia" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
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 On Jul 25, 3:04*pm, pgarcia wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
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 "Susan" wrote: 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 On Jul 25, 2:14 pm, pgarcia wrote: 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:14Â*7,160:163,164:167,176:1 77,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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
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 "pgarcia" wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
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 -- HTH... Jim Thomlinson "Susan" wrote: 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 On Jul 25, 2:14 pm, pgarcia wrote: 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:14Â*7,160:163,164:167,176:1 77,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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
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. -- HTH... Jim Thomlinson "Susan" wrote: 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 On Jul 25, 3:04 pm, pgarcia wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
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 "FSt1" wrote: 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 "pgarcia" wrote: 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
3 strikes & i'm out.................
:) susan On Jul 25, 3:50*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: 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 "FSt1" wrote: 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 "pgarcia" wrote: 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- Hide quoted text - - Show quoted text - |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
Ok, I did that and it still stops. I tried just about everyones suggestion
and it does not see to work. I guess I need a work around. Can I email you the spread sheet? "Susan" wrote: 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 On Jul 25, 3:04 pm, pgarcia wrote: 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
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" wrote: 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. -- HTH... Jim Thomlinson "pgarcia" wrote: 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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
Send away...
-- HTH... Jim Thomlinson "pgarcia" wrote: 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" wrote: 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. -- HTH... Jim Thomlinson "pgarcia" wrote: 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 |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
You are only at 2 strikes and you have learned 2 things. Not a bad day in my
books... -- HTH... Jim Thomlinson "Susan" wrote: 3 strikes & i'm out................. :) susan On Jul 25, 3:50 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: 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 "FSt1" wrote: 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 "pgarcia" wrote: 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- Hide quoted text - - Show quoted text - |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Option Button with VB code, not working
Thanks Jim, but I did a work around. I created a valuation drop down list and
that worked out great. Still... "Jim Thomlinson" wrote: Send away... -- HTH... Jim Thomlinson "pgarcia" wrote: 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" wrote: 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. -- HTH... Jim Thomlinson "pgarcia" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Option Button code not working | Excel Discussion (Misc queries) | |||
| How do I select an option button using code | Excel Programming | |||
| How to assign same code inside Option button code space ?? | Excel Programming | |||
| Code to tab from option button click | Excel Programming | |||
| Old button code not working in Excel2002 | Excel Programming | |||
Linear Mode
