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.

 

[Zarqy]

The other procedures are mainly using the standard file dialog boxes,
and controlling serial port devices.

Any suggestions or ideas as to why these would interfere with this
listbox?

- Daron

 

[Tom Ogilvy]

No -

You can use a global boolean variable to block events

Public bBlockEvents as Boolean

in each event or procedure (where appropriate) check the value as the first
thing. If it is true, then exit that event or procedure. If not then that
procedure sets it to true (to block other code). do the normal code you
have, then at the end set it to false.

This may help.