asked on

Change Multi-select property through VBA

I would like to get some assistance with dynamically changing a form's **multi-select** property through VBA (without having to close/re-open the form).

Current process:
- I have one listcontrol ("ListBoxActions") with 4 tabs on a form
- In the listbox's property sheet, the value for **multi-select** = "None"
- Upon clicking tab 1, 3, and 4, I would like to have the multi-list property set to "1" (single).
- Alternatively, upon clicking tab 2, I would like to dynamically change the multi-list property to "2" (extended).

I have tried two approaches (see below):

           Me.ListBoxActions.MultiSelect = 2   ' Extended

Open in new window

and/or

           Forms("frmLogin").Controls("ListBoxActions").MultiSelect = 2   ' Extended

Open in new window

Unfortunately, neither of the two methods work. That is, once I click on tab 2, I only can select a single value (vs. multiple values) from the listbox.

Below is the full code for the listbox. What am I missing? How should the VBA be modified to allow dynamic switching of the multi-select property?

Thank you,
EEH

Private Sub TabControlObject_Change()

    Select Case Me.TabControlObject.Value
               
        Case 0
           MsgBox "1st tab"       
           'Me.ListBoxActions.MultiSelect = 1
           Forms("frmLogin").Controls("ListBoxActions").MultiSelect = 1 'Simple
           Me.ListBoxActions.RowSource = "SELECT tblTabControlRowSources.RowSource_Page_1 FROM tblTabControlRowSources WHERE (((tblTabControlRowSources.RowSource_Page_1) Is Not Null));"
           Me.cmd_RunQuery.Visible = False
          
        Case 1
           MsgBox "2nd tab"
           'Me.ListBoxActions.MultiSelect = 2
           Forms("frmLogin").Controls("ListBoxActions").MultiSelect = 2 'Extended
           Me.ListBoxActions.RowSource = "SELECT tblTabControlRowSources.RowSource_Page_2 FROM tblTabControlRowSources WHERE (((tblTabControlRowSources.RowSource_Page_2) Is Not Null));"
           Me.cmd_RunQuery.Visible = True
           
        Case 2
           MsgBox "3rd tab"
           'Me.ListBoxActions.MultiSelect = 1
           Forms("frmLogin").Controls("ListBoxActions").MultiSelect = 1 'Simple
           Me.ListBoxActions.RowSource = "SELECT tblTabControlRowSources.RowSource_Page_3 FROM tblTabControlRowSources WHERE (((tblTabControlRowSources.RowSource_Page_3) Is Not Null));"
           Me.cmd_RunQuery.Visible = False
           
        Case 3
           MsgBox "4th tab"
           'Me.ListBoxActions.MultiSelect = 1
           Forms("frmLogin").Controls("ListBoxActions").MultiSelect = 1 'Simple
           Me.ListBoxActions.RowSource = "SELECT tblTabControlRowSources.RowSource_Page_4 FROM tblTabControlRowSources WHERE (((tblTabControlRowSources.RowSource_Page_4) Is Not Null));"
           Me.cmd_RunQuery.Visible = False
           
   End Select
        
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ExpExchHelp

ASKER

Ok... thank you, I thought about the process of using distinct listboxes for each tab.

Naturally, I can place them at equal top/left positions. However, I didn't really like that idea given that 3 listboxes would be hidden (placed on top of each other). Thus, in the event I would have to make adjustments to any of the properties, I would have to temporarily move all four (or two) listboxes away.

Do you have a recommendation so that the administrators can easily see all existing listboxes in Design view, but the user sees them at same positions? I hope this makes sense.

Thank you,
EEH

SOLUTION

Hamed Nasr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

Rather than futzing with the properties of the controls, use your validation code to determine if multiple items were selected. If True, then check your "multi-select" property to ensure it is 2.

ExpExchHelp

ASKER

Thank you for your assistance. I appreciate it.

PatHartman

The reason for NOT changing design attributes of an application is that doing so prevents you from distributing as an .accde/.mde or running via the runtime engine.

ExpExchHelp

ASKER

Quick follow-up... what's the VBA (I presume for "OnOpen" function) that positions various listboxes at the same "left" & "top" position?

Thank you,
EEH

PatHartman

If you have a new question, please start a new thread.

ExpExchHelp

ASKER

Not a new question... hnasr hinted on positioning the listboxes.

Scott McDaniel (EE MVE )

You just set the Top and Left properties the same:

Me.Listbox1.Top = SomeValue
Me.Listbox2.Top = SomeValue

ExpExchHelp

ASKER

Thank you, Scott.

For testing purposes, I choose the left and top properties of my 2nd listbox. Its values are 0.6979", 1.2146", respectively.

Upon opening the form, the listbox was in the top left corner of my form. I saw the listbox move only once I have the value from "0.6979", 1.2146" to e.g., "5000". Apparently, there are different ratios for this process. It would be tricky for me to find the exact top/left position.

My question: Is there a formula to convert the litstbox's position property values of 0.6979", 1.2146" to its equivalent position values for the form?

Thank you,
EEH

Scott McDaniel (EE MVE )

You need to convert those values to Twips:

Me.Listbox1.Top = YourValue * 1440

1440 is a constant, which is essentially the number to Twips per Inch.

ExpExchHelp

ASKER

Thank you, Scott... I appreciate it.

Hamed Nasr

If your reference object is ListBox1 then:
in Form_Open event:

ListBox2.Top=ListBox1.Top
ListBox2.Left=ListBox1.Left

ListBox3.Top=ListBox1.Top
ListBox3.Left=ListBox1.Left

Open in new window

ExpExchHelp

ASKER

Thank you, hnasr.

Hamed Nasr

Welcome!

Change **Multi-select** property through VBA

Change Multi-select property through VBA