HOW TO REMEMBER WHICH ITEM ON WHICH ROW YOU ADDED THE VALUE

jamielill

Active Member
Joined
Jul 27, 2009
Messages
286
Hi,
I have 30 check boxes per say, and if i set a check box to true it adds a numeric value to a list box.
Each checkbox has a value of 5
So if i click 5 checkbox s
my list box would be
5
5
5
5
5
What I want to do is remember which row each checkbox added the value so when I set false a checkbox it will remove it from the list box
So if i set checkbox 4 to false and this was added my third selection added to the list box it would remove row 3
5
5
5
5
Then all of the other position would change and have to be recorded.

Is there an easy way to do this?

thanks
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,274
Office Version
2013
Platform
Windows
Is there an easy way to do this?
The short answer is "No". You would need to use a helper list somewhere to track your changes to the checkboxes, ergo, the list box.. Maybe an additional column in the list box to shyow the last checkbox used for that value, or a two column range where the checkbox name and listbox position are cross referenced. But in either case it would take code to make and maintain the entries, which I have no desire to write. Maybe someone else has a better idea.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,206
Office Version
365
Platform
Windows
If each checkbox puts 5 into the listbox, what difference does it make which one you remove? They're all the same
 

jamielill

Active Member
Joined
Jul 27, 2009
Messages
286
If each checkbox puts 5 into the listbox, what difference does it make which one you remove? They're all the same
They will not be the same that is an example. I THINK I SHOULD USE A 2D MATRIX THEN REPOPULATE THE LIST BOX EACH TIME FROM THE MATRIX. UNLESS THETE IS A BETTER WAY
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,206
Office Version
365
Platform
Windows
You could do it like that, or as JLGWhiz suggested, have an extra column on the list box.

In future please do not SHOUT (using all capitals) as it is considered rude.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,819
Office Version
2007
Platform
Windows
Is there an easy way to do this?
A simple way is to put the 30 choices in the listbox.
You use the MultiSelect and ListStyle properties.
That way you don't have to remember the row or pass the data to the listbox.

1575726591775.png



Try this:

VBA Code:
Private Sub UserForm_Activate()
  Dim i As Long
  With ListBox1
    .MultiSelect = fmMultiSelectMulti
    .ListStyle = fmListStyleOption
    For i = 1 To 30
      .AddItem "choice " & i
    Next
  End With
End Sub
 

Attachments

jamielill

Active Member
Joined
Jul 27, 2009
Messages
286
A simple way is to put the 30 choices in the listbox.
You use the MultiSelect and ListStyle properties.
That way you don't have to remember the row or pass the data to the listbox.

View attachment 1516


Try this:

VBA Code:
Private Sub UserForm_Activate()
  Dim i As Long
  With ListBox1
    .MultiSelect = fmMultiSelectMulti
    .ListStyle = fmListStyleOption
    For i = 1 To 30
      .AddItem "choice " & i
    Next
  End With
End Sub
Thank you every for you input
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,819
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,274
Office Version
2013
Platform
Windows
The key issue is to remove the value that is tied to a specific row number. So the listbox values have to be cross referenced in some manner to the row at the time of entry, else there is not way to know which row prompted the checkbox to be clicked, nor where the entry was made in the listbox. The illustration in the OP is apparently not really representative of what one might see after check boxes are ticked, according to Post #4. So the OP wants to remove the value for the specific row that initiated the entry. Like I said, that will take some code to do the tracking of the initial entry and subsequent changes.
 

Forum statistics

Threads
1,081,986
Messages
5,362,561
Members
400,681
Latest member
mariscann

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top