Using listbox as menu on userform

[Zarqy]

I am trying to use a listbox on my form as a 'menu' in order to
eliminate many buttons, and could use some help.

The listbox is linked to a range on the ss called "_Menu_Action" in
order for the items to be somewhat dynamic. (copy of range is at end of
message with notes)

This work fine, but for the following problems:

1) Cannot get the items to unselect
- even using the MouseUp event and lbo_MenuData.ListIndex = -1

2) Endless looping (change = click?)
- I change the source to indicate if the datafile should be opened or
closed
This seems to evoke the click event, which puts this into a loop
- I have spent many hours googling, and cannot seem to fix this

Any Help would be greatly appreciated!

(watch for wrap...)
<-= CODE BEGIN =->

Private Sub lbo_MenuData_Click()
'-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
' Start: February 01 2005
' Author: Daron S. Lowell
' Purpose: To give user a single place to initiate an action on the
' Data tab
' - User will click on an item to initiate action
' Why: Simplify interface
' Future:
'-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Dim int_Row As Integer 'Which row currently has my data
Dim int_Offset As Integer 'Row offset of current menu item
Dim rng_Range As Range 'Range of the Action Menu
Dim rng_Value As Range 'Current Value of Menu Item modifier

Select Case lbo_MenuData.Value
Case "Grid"
btn_OpenConfig_Click

Case "Data"

Set rng_Range =
ThisWorkbook.Worksheets("Default_Values").Range("_Menu_Action")

'need to find ?relative? location in order to update
'OPEN or "CLOSE"

'int_Offset = row that the Data menu item is in -
' top row of _Menu_Action
int_Offset = rng_Range.Find(What:=lbo_MenuData, _
LookIn:=xlValues, _
SearchOrder:=xlByColumns).Row - rng_Range.Row

Set rng_Value = rng_Range.Cells(1, 1).Offset(int_Offset, 3)

Select Case rng_Value
Case "Open"
'Test if this is an endless menu loop!
'tf_MenuToggle is a global boolean
If Not tf_MenuToggle Then

tf_MenuToggle = True
btn_OpenDataFile_Click

rng_Value = "Close"

Else
tf_MenuToggle = False
End If

Case "Close"
'Test if this is an endless menu loop!
If Not tf_MenuToggle Then

tf_MenuToggle = True
btn_CloseFile_Click
rng_Value = "Open"

Else
tf_MenuToggle = False
End If

End Select

lbo_MenuData.Value = ""

Case "Rectr"
btn_Recenter_Click

Case "Cal"
btn_Calibrate_Click

Case "Free"
btn_FreeSpace_Click

Case "View"
ckbx_ViewDataSheet_Click

End Select

End Sub

Private Sub lbo_MenuData_MouseUp(ByVal Button As Integer, ByVal Shift
As Integer, _
ByVal X As Single, ByVal Y As Single)
'-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
' Start: February 02 2005
' Author: Daron S. Lowell
' Purpose: To unselect the lbo_MenuData menu/ListBox
' Why: Two purposes:
' 1) To unselect the current item
' 2) Prevent the Click event from being invoked twice
' - If this line is in the _Click event, the _Click event
occurs twice
' and still will not clear the select item
' Ref URL: http://support.microsoft.com/kb/q211759/
'
http://groups-beta.google.com/group..._doneTitle=Back+to+Search&&d#90f302aad23f2bb8
' - (Article 6)
' Future:
'-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

lbo_MenuData.ListIndex = -1

End Sub

<-= CODE END =->

<-= Range("_Menu_Action") BEGIN =->

Data Menu: Abbrev. Sort Change
Use Grid File... Grid 1
Open Data File... Data 2 Open
Recenter Rectr 3
Calibrate Cal 4
Freespace Free 5
View Data Sheet View 6 View

<-= Range("_Menu_Action") END =->
Notes:
- The listbox uses the first two columns.
- The "Abbrev." column is the data column
- "Sort" column for later use
- "Change" column acts as modifier column. This can be changed with
the above code. Formulas in the "Data Menu:" column to link this and
an out-of-range column to obtain the results

 

[Tom Ogilvy]

Use MouseDown, not MouseUp

Private Sub ListBox1_Click()
MsgBox "In click, value: " & ListBox1.Value & _
vbNewLine & " index: " & ListBox1.ListIndex
End Sub

Private Sub Listbox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

ListBox1.ListIndex = -1
End Sub

Works for me. Perhaps that will halt your recursive calls.

 

[Zarqy]

Tom,

I just tried your suggestion. I still get the recursion from the data
change evoked in the Click event

Daron

 

[Tom Ogilvy]

I can't fix that - your click event is using all kinds of external stuff and
procedures. No telling what the interactions are.