Tell a macro to check if object exists and if yes do A. but if no then do B.?
I use MS Excel 2007.
I have the following code in a macro that creates two Option Buttons (not active X) into Cell B25.
Range("B25").Select
ActiveSheet.OptionButtons.Add(129.75, 540, 24, 20.25).Select
Selection.Name = " Select1Button "
Range("B25").Select
ActiveSheet.OptionButtons.Add(225.75, 540, 79.5, 21.75).Select
Selection.Name = " Select2Button "Is there a way for the macro to check if either already exists in Cell 'B25' and if they do then do nothing and finish the rest of the Macro but if they do not then create them as described above?
Something like:
In CellB25 does "Select1Button" & "Select2Button" Exist?
Yes = Then ignore the ActiveSheet.OptionButtons.Add code and continue to run
the rest of the macro code.
No - Then run the ActiveSheet.OptionButtons.Add code and continue to run the
rest of the macro code.I have been stuck on this for hours!
12 Answers
I would like to suggest you the solution in two parts.
Part one will help you to create Option Button in Cells.
And Part two will help you to determine that which Option Button has been clicked, to execute further actions.
Part 1:
This VBA (Macro) will help you to create Two Option buttons in Cell C1 & D1 with Group/Frame.
Sub AddOptionButtons() Dim btn1 As OptionButton Dim btn2 As OptionButton Dim btn3 As OptionButton Dim grbox As GroupBox Dim t As Range Dim s As Range Dim i As Integer ActiveSheet.OptionButtons.Delete ActiveSheet.GroupBoxes.Delete For i = 1 To 1 Step 1 Set t = ActiveSheet.Range(Cells(i, 3), Cells(i, 3)) Set s = ActiveSheet.Range(Cells(i, 4), Cells(i, 4)) Set btn1 = ActiveSheet.OptionButtons.Add(t.Left, t.Top, t.Width, t.Height) Set btn2 = ActiveSheet.OptionButtons.Add(s.Left, s.Top, s.Width, s.Height) Set grbox = ActiveSheet.GroupBoxes.Add(t.Left, t.Top, t.Width + 50, t.Height) With btn1 .Caption = "" .Display3DShading = True .LinkedCell = "E" & i End With With btn2 .Caption = "" .Display3DShading = True End With With grbox .Caption = "My Group" .Visible = True End With Next i
End SubHow it works:
- Copy & Paste this code as Standard Module.
For i = 1 To 1 Step 1determines that2 Option Buttonswill be created inRow 1.- If you need to create
4 Option Buttonsthe it should be,For i = 1 To 2 Step 1. ActiveSheet.Range(Cells(i, 3)determines thatiisRow valueand3isColumn, are editable.Lined Cell to Option ButtonsisE1, which is editable.
Part 2:
This Macro will help you to determine that which Option Button has been Clicked to take further action.
Sub TheSelectCase() Select Case Range("E1").Value Case 1 Your Code for further action. Case 2 Your Code for further action. End Select
End SubDraft code:
public sub createoptionbutton(xname,a,b,c,d)
dim obj as object
xname=trim(xname)
for each obj in activesheet.optionbuttons if obj.name=xname then exit sub
next
activesheet.optionbuttons.add(a,b,c,d).select
selection.name=xname
end subEdit it as you need.
PS. You do not need to select any cell before optionbox creation - it doesn't matter. Simply point to a specific worksheet instead of ActiveSheet. And I'd recommend to set the GroupBox property additionally.
PPS. At the request of Rajesh S - the function which checks the optionbutton existence only:
public function optionbuttonexists(xname) as boolean
dim obj as object
xname=trim(xname)
for each obj in activesheet.optionbuttons if obj.name=xname then optionbuttonexists = true exit sub end if
next
end function ... and backward function
public function optionbuttonabsent(xname) as boolean
dim obj as object
xname=trim(xname)
for each obj in activesheet.optionbuttons if obj.name=xname then exit sub
next
optionbuttonabsent=true
end function The last function allows to solve author's task as
if optionbuttonabsent(" Select2Button ") then ActiveSheet.OptionButtons.Add(225.75, 540, 79.5, 21.75).Select Selection.Name = " Select2Button "
end if
