 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / July 2008Option 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 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 > Why dosn't this work? Thanks > Sheets("INPUT_A").Select [quoted text clipped - 30 lines] > Call AR_ECO > End If 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 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 > Will, it stops at "If OptionButton2.Value = True Then" and it will not > continue from there. This is the full code. [quoted text clipped - 39 lines] > > End Sub 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.  SignatureHTH... Jim Thomlinson > try this first & see if it gets past that first line. change the > worksheet name to the proper name. [quoted text clipped - 49 lines] > > > > End Sub 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? > try this first & see if it gets past that first line. change the > worksheet name to the proper name. [quoted text clipped - 49 lines] > > > > End Sub 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 > Will, it stops at "If OptionButton2.Value = True Then" and it will not > continue from there. This is the full code. [quoted text clipped - 39 lines] > > End Sub There is no issue with deleteing non-consecutive rows... This works just fine... Sub test() Range("3:5, 7:10").Delete End Sub SignatureHTH... Jim Thomlinson > hi > i thinks that is because you are trying to delete non consecutive rows. no [quoted text clipped - 47 lines] > > > > End Sub 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... [quoted text clipped - 60 lines] > > - Show quoted text - You are only at 2 strikes and you have learned 2 things. Not a bad day in my books... SignatureHTH... Jim Thomlinson > 3 strikes & i'm out................. > :) [quoted text clipped - 66 lines] > > > > - Show quoted text - 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 > Why dosn't this work? Thanks > Sheets("INPUT_A").Select [quoted text clipped - 30 lines] > Call AR_ECO > End If 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 > 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, [quoted text clipped - 67 lines] > > Call AR_ECO > > End If 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 SignatureHTH... Jim Thomlinson > 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, [quoted text clipped - 67 lines] > > Call AR_ECO > > End If 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. SignatureHTH... Jim Thomlinson > Why dosn't this work? Thanks > Sheets("INPUT_A").Select [quoted text clipped - 30 lines] > Call AR_ECO > End If 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? > 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 [quoted text clipped - 55 lines] > > Call AR_ECO > > End If Send away... SignatureHTH... Jim Thomlinson > Ah, I wrote, sheet1., but did not get Options buttons. I will see if I can > work around this. [quoted text clipped - 59 lines] > > > Call AR_ECO > > > End If Thanks Jim, but I did a work around. I created a valuation drop down list and that worked out great. Still... > Send away... > [quoted text clipped - 61 lines] > > > > Call AR_ECO > > > > End If |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.