Control Objects

  • Thread starter Thread starter Stewart
  • Start date Start date
S

Stewart

I have put some option button controls on a worksheet, the
worksheet is quite large and I want the user to be able to
pick options as they move around the sheet to look at the
data. Is there a way to lock the position of the controls
so the user can see and use them whilst looking at all
parts of the sheet. This solution cannot use freezing
panes for various reasons.

I would really appreciate some help on this!!!!!

Thank you in advance.

Stewart
MOS Excel Expert (not good enough though!!)
 
Freezing panes is the obvious way to do this. If that's out some other
choices that pop immediately to mind are:

-A second window along side the primary one, where the option buttons would
be.
-A modeless userform.
-A custom menubar.

The third option I listed doesn't support conventional option buttons, but
that doesn't mean you cannot achieve the same affect. I've thrown together
some code to show you what I mean.
--
Jim Rech
Excel MVP


Dim Mn As CommandBarControl

Sub MakeCmdbar()
DelCmdbar
With CommandBars.Add("OptPicker", msoBarFloating, False, True)
Set Mn = .Controls.Add(msoControlPopup)
Mn.Caption = "Options: Currently None"
AddMnItem Mn, "First Choice", 1
AddMnItem Mn, "Second Choice", 2
AddMnItem Mn, "Third Choice", 3
AddMnItem Mn, "Fourth Choice", 4
.Visible = True
End With
End Sub

Sub DelCmdbar()
On Error Resume Next
CommandBars("OptPicker").Delete
End Sub

Sub AddMnItem(Owner As CommandBarControl, Cap As String, Param As String)
With Owner.Controls.Add(msoControlButton, , 1)
.Caption = Cap
.OnAction = "ClickHandler"
.Parameter = Param
End With
End Sub

Sub ClickHandler()
Dim CtrlNum As Integer
ResetState
CtrlNum = CInt(CommandBars.ActionControl.Parameter)
Mn.Controls(CtrlNum).State = msoButtonDown
Select Case CtrlNum
Case 1
Mn.Caption = "Options: Currently 1"
''Do other stuff
Case 2
Mn.Caption = "Options: Currently 2"
Case 3
Mn.Caption = "Options: Currently 3"
Case 4
Mn.Caption = "Options: Currently 4"
End Select
End Sub

Sub ResetState()
Dim Ctrl
For Each Ctrl In Mn.Controls
Ctrl.State = msoButtonUp
Next
End Sub
 
Back
Top