ACTIVEX EMBEDDED LISTBOX: MOVE ALWAYS TO LAST ITEM IN THE LIST (after executing previous macro)

ehemad

New Member
Joined
Dec 27, 2021
Messages
4
Office Version
  1. 365
Hello

i have created a listbox with the active X option, the list box is getting a list of clients from a cell range, and when selecting any client from the list it triggers a macro that do different things depending on the client chosen. that works great.

The problem is that the last selecction always remains selected (in blue) and an additional click (if the next task is again for the same client) it is not possible unless you click somewhere else before clcicking same item again.

i have defined the last item of the client list as "-" and if clicking here, no macro is deployed, so nothing happens, now you can choose (make a click) again from any item on the list and execute the macro.

Is there any way to tell the macro when it is finished to move the blue ribbon (selection) to the last item in the list( "-")??? so i can skip clicking it manually after every time i choose a client from the list?

Thanks in advance
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
To put the highlight on the last item, set the ListIndex to the ListCount. For example...
VBA Code:
Sheets("Sheet1").ListBox1.ListIndex = Sheets("Sheet1").ListBox1.ListCount - 1

However, you don't need to use your work-around at all... simply set the ListIndex to -1 (minus one) and the blue highlight will be removed completely...
VBA Code:
Sheets("Sheet1").ListBox1.ListIndex = -1
 
Last edited:
Upvote 0
In the macro you say you have... my guess is at the point you want to set focus on the "-" item. I would remove the dash item from your ListBox and put the line of code that sets the ListIndex to -1 at that location.
 
Upvote 0
In the macro you say you have... my guess is at the point you want to set focus on the "-" item. I would remove the dash item from your ListBox and put the line of code that sets the ListIndex to -1 at that location.
Hi again, and thanks for taking the time.
the thing is that the listbox was created using the excel tools (developer/insert/activexcontrols/listbox) and not with code, the macro i mentioned is a normal macro doing some stuff depending on the item selected. (and i guess there is not point to place it there, unless you tell me otherwise)
i tried placing the code line in the module running that worksheet but it did not work
sorry, but as you can see just started with vba and learning basic stuff...
thanks again for your time

the module for that worsheet is as follows:

listbox1 [click]
private sub deltah_click()
call deltahedge
endsub
-------------------
privatesub Individuals_click ()
call individuals
end sub
-------------------
private sub listbox1_click()
listbox1.listboxindex = -1 (your code here)
call sendmail (my macro, that sends an email with info based on listbox selection)
end sub
 
Upvote 0
Rick, hey, i found an interesting comment you made in 2011... and it is exactly what is happening when using you code line, i lose my refrerence appearing N/A... that is why it seems not to be working...
how do i solve this? storing the info in a global variable? and how can i do that?
thanks again

your 2011 post:
That is how a ListBox works... the highlighted line is an indicator to the user which item is currently selected. It also allows your program to reference the item via the ListIndex property. There is a way to do what you want (possibly dependent on where exactly you are running your code from) but then your user and your program will lose access to the selected item (unless you store that information in a global variable before hand). To remove the highlighting (and lose the reference to the selected item, just set the ListIndex property to -1 (minus one).
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top