Listbox to insert data to selected line

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows
Hi

currently I have a userform with a listbox that displays the data I've selected in textboxes but whatever code I try to insert the new data to either the listbox or cells of the highlighted line in the list box I get error "70" listbox does not have permission.

1597138917425.png
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,095
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
How is your listbox populated? Using RowSource? If so, you'll need to add the data to the source worksheet and then update the rowsource property to include the new row.
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows
Yeah the list box is populated using the RowSource but can change if needed as im wanting to use this userform to update the values of the selected row.
At the moment I have 1 Userform for inserting the data to the source worksheet and then this userform which will display all data in that work sheet which can then be updated from this second userform
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,095
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can still use Rowsource, you just need to update the sheet, not the listbox, as I mentioned. Personally, I don't like to directly link controls to worksheets unless necessary, but it looks like you are using column headers, which does require the use of rowsource.
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

By updating the worksheet do you meaning updating it by using the data entered into the textboxes and then having the listbox refresh to update the new values?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,095
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Yes. The refresh should happen automatically.
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

would it be possible to update the cells of the row that's selected in the listbox?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,095
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Yes, you can update any cells you like. Just be aware that changing cells that are part of the Rowsource will most likely clear the selection in the listbox, so you should store the current listindex and reset it again.
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows
Instead of using the rowsource how would I go about populating the list box and then also what code would I use to update the cells from the userform?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,095
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you don't use Rowsource, you won't be able to use column headers which look to be fairly important given the number of columns in your listbox.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

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
Top