On Sat, 4 Aug 2012 12:41:39 -0700 (PDT), noname wrote:
On Sunday, August 5, 2012 1:00:47 AM UTC+5:30, Ron Rosenfeld wrote:
On Sat, 4 Aug 2012 10:50:26 -0700 (PDT), noname wrote:
you are right. i need the one-liner code for something else. see attached file.
http://sdrv.ms/NsOdAV
when we use form Groupboxes & OptionButtons on a worksheet, to select those OptionButtons to hide/unhide them, we have to loop through all the optionbuttons in a worksheet i.e. Activesheet.optionbuttons.visible=true/false.
I have named my optionbuttons as "opt_" & cells(i,j).topleftcell.address. i don't want to hide/unhide all the optionbuttons in a worksheet, but the ones found in chosen Region rows. i have used checkboxes to tick/untick Region rows. Now, looping thru all the activesheet.optionbuttons and matching the correct optionbutton name with the topleftcell, takes time. Also, there is some major problem with hiding/unhiding the optionbuttons, ie. they donot hide with the rows but shift down or up.
Complicated code.
But I still don't see a way of doing it with a one-liner in VBA.
However, the fact that you have control over the input simplifies the code considerably, but I still think you'll have to loop through the address range in order to get them out. Especially since the individual cell addresses are not a property of the range object. You could put the addresses into an array, and then use them to find your controls, I suppose. You'll need someone more knowledgeable than I to help further, though.
Yes, thats what i have in mind, ie. once i have the split addresses in a string e.g StrAddress, then something like,
with Activesheet
.optionbuttons(Array(StrAddress)).visible=false/true.
end with
It's easy to set up the array, especially if you can control your input type:
========================================
Option Explicit
Sub ClrRange()
Dim r As Range, c As Range
Dim s() As String 'or possibly as range depending on the required
'argument type for your option buttons property
Dim i As Long
Set r = Application.InputBox("Clear Range: ", Type:=8)
ReDim v(0 To r.Count - 1)
For Each c In r
s(i) = c.Address
i = i + 1
Next c
'With ActiveSheet
' .optionbuttons(s).visible=false/true.
'End With
End Sub
================================