Change listbox value

[Stingone]

Hello,

I have created a sheet with a rowsource from A1:L19 in sheet1

What i want to do is change the value's in a userform. I have an example of how i like to see it.

http://www.excelforum.com/excel-prog...x-records.html

But in that case i need to create for every cell a separate textbox. Is there a easier way to manage/change this? I only need to have the buttons edit and change. How can i manage this?
See attached fileChange listbox.xlsm

[jaslake]

Hi Stingone

This code is in the attached...let me know of issues.

Please Login or Register  to view this content.

[Stingone]

Hi Stingone

This code is in the attached...let me know of issues.

Please Login or Register  to view this content.

When testing it gives an error "cant find library in Private Sub UserForm_Initialize()

[Stingone]

Error solved, problem with web components 11. The form is now showing but the values are not updated in the listbox when clicking on modify. The sheet update is working though

[jaslake]

Hi Stingone

Yes, the code as written will NOT update the ListBox...only the Sheet. Replace the code with this...it also updates the ListBox

Please Login or Register  to view this content.

[Stingone]

Works fine .. is there also a way that the textboxes can not be updated empty? when i now click on update with all field empty it gives an error

[jaslake]

Hi Stingone

I'm missing something in your explanation. I can fill all the TextBoxes with blanks and it updates as expected and no error is reported.
Please explain again.

[Stingone]

Hi Stingone

I'm missing something in your explanation. I can fill all the TextBoxes with blanks and it updates as expected and no error is reported.
Please explain again.

If you open the excel file and the userform disapears when clicking directly on the modify button without selecting a row it gives an 1004 error.

[jaslake]

Hi Stingone

Ah...I see said the Blind Man. Replace the CommandButton2_Click code with this

Please Login or Register  to view this content.

Let me know of issues.

[Stingone]

If you open the excel file and the userform disapears when clicking directly on the modify button without selecting a row it gives an 1004 error.

Great you are the best is there also a posibility to exclude blank rows?

[jaslake]

Hi Stingone

A method to exclude them does not come to mind but we can make them inoperative if that works for you.
Try this code

Please Login or Register  to view this content.

[Stingone]

How can i show a specific part of the worksheet in the listbox instead of the whole worksheet?

[jaslake]

Hi Stingone

For example?

[Stingone]

Hi Stingone

For example?

Hi Jaslake...

I only want to display and change the range D10:M550. And is it possible to get in every multipage a different listbox input to change? and how ?

[Stingone]

Hi Stingone

For example?

Hi Jaslake...

I only want to display and change the range D10:M550. And is it possible to get in every multipage a different listbox input to change? and how ? and is it also possible to show the format as displayed in the worksheet?

[Stingone]

Hi Stingone

For example?

Hi Jaslake...

I only want to display and change the range D10:M550. And is it possible to get in every multipage a different listbox input to change? and how ? and is it also possible to show the format as displayed in the worksheet?

[jaslake]

Hi Stingone

Regarding this

is it possible to get in every multipage a different listbox input to change?

...don't know...what's your UserForm look like...the one I see from your original upload is not Multi-Page.

[Stingone]

Hi Stingone

Regarding this...don't know...what's your UserForm look like...the one I see from your original upload is not Multi-Page.

See attachment. So on mulipage tab1 is connect to sheet1, i want tab2 connected to sheet2 etc etc. Value sheet1 for example D1:K12. Value sheet2 E1:K14.

Gegevens veranderen.xlsm

[Stingone]

Please Login or Register  to view this content.

I now can load in each tab a listbox, but for page2 it updates the entire row beginning at cell 13. When i change the command button 4 from 1 till 12 it empty's the whole row... what am i doing wrong? Attached file see what happens when i update values on page2..Gegevens veranderen.xls

So im almost there, but now only the right update and only the range D1:L19 instead of whole sheet

[Stingone]

Gegevens veranderen.xls Here another example that's not working with a range.

[jaslake]

Hi Stignone
Change your ComandButton4 code to this

Please Login or Register  to view this content.

[Stingone]

Hi Stignone
Change your ComandButton4 code to this

Please Login or Register  to view this content.

Can you maybe provide the code to change the range for example D10:M50 instead of UsedRange? and what i need to change in the rest of the sheet?

Please Login or Register  to view this content.

[jaslake]

Hi Stignone

This makes no sense to me in relationship to your posted file

Can you maybe provide the code to change the range for example D10:M50 instead of UsedRange?

Explain what it is you're trying to do...I don't follow.

[Stingone]

See code above... i have a lot of data in a worksheet but only want to show/change a part of the worksheet instead of the whole sheet.

What i did is just replace the UsedRange with Range("D10:M50"). In the listbox it shows it correctly but when i click on the list it gives me errors same for changing.

See attachment. I only want to show the range specified and also edit this range in the worksheet instead of the whole sheet. Change range.xls I don't see it

[jaslake]

Hi Stignone

I assume you tried your revised code...what happens?

[Stingone]

It works fine until i try to specify the range. and change the collumns etc.

[jaslake]

I see your new attachment...I'll see if I can figure out what you're trying to do from looking at it and the code...get back to you.

[jaslake]

Hi Stignone

What error message are you getting?

[Stingone]

Couple of error e.g. cant find list property very large error in numbers. you see it when opening the attachment

[jaslake]

Hi Stignone

See if this helps.

[Stingone]

Hi Stignone

See if this helps.

Please Login or Register  to view this content.

This should work using your example. But when clicking on the update button nothing happen

[Stingone]

Hi Stignone

See if this helps.

Please Login or Register  to view this content.

This should work using your example. But again getting error 381

[jaslake]

Hi Stignone

In the File I uploaded there is no UpDate Button...there is a Modify Button. So, are you saying "Nothing happens when you click the Modify Button" in the Sample File I posted?

[Stingone]

Hi John,

No your file is working Ok. but see the last code i have provided. That is my complete model. But getting the error 381. I used your code as example

[jaslake]

Hi Stignone

I can debug code on my file with my code but I've no way of knowing how to debug your code on your file without seeing your file and your code. Think about it.

Be happy to help...if I can.

[Stingone]

Hi Stignone

I can debug code on my file with my code but I've no way of knowing how to debug your code on your file without seeing your file and your code. Think about it.

Be happy to help...if I can.

Attached the original file. In worksheet INPUT BS i have the DATA. Red marked i not want to change only display in listbox. Green marked needs to be changed.

If you now run the file you will get the error 381.

Really hope you can help me out.

Original File.xlsm

[jaslake]

Hi Stignone

See if the attached gets you started. The UserForm loads and the Pages get populated with what appears to be the correct ranges of data. I've done nothing with Formatting.

Please note, in previous Sample Files your Ranges ALWAYS started at Row 1. In this new File none of the Ranges start at Row 1. So, in these lines of Code

Please Login or Register  to view this content.

none of the Row Indexes will be correct (the wrong row will be changed). I'll play around with this issue a bit today and see if I can resolve it.

If you have particular items you DON"T want to allow change, change the TextBox Enabled Property to False. See TexBox49 on Page 5 for an example.

Let me know of issues.

[jaslake]

Hi Stignone

I've added some Public Variables (see Module1) and I've changed the Initialize Event Code to capture the Range Start Row for each of your Page Ranges.
I've modified the UpDate Button Code for Page 1 and Page 5 (CommandButton12 and CommandButton10); please make the same appropriate modifications on Page 2, 3 and 4 Update Buttons (CommandButton4, CommandButton6 and CommandButton8)

Let me know of issues.

[Stingone]

Hi Stignone

I've added some Public Variables (see Module1) and I've changed the Initialize Event Code to capture the Range Start Row for each of your Page Ranges.
I've modified the UpDate Button Code for Page 1 and Page 5 (CommandButton12 and CommandButton10); please make the same appropriate modifications on Page 2, 3 and 4 Update Buttons (CommandButton4, CommandButton6 and CommandButton8)

Let me know of issues.

Hi John,

Many thanks for your help works great now... can you maybe help me out with one more issue? i have some rows hidden in my original worksheet. How can i hide these in my listbox? there is thread, but i cant get it implemented in my code

http://www.excelforum.com/excel-prog...55#post3034455

[jaslake]

Hi Stignone

In the UserForm Initialize Event, for each ListBox, change this line of code

Please Login or Register  to view this content.

[Stingone]

Hi John, I implemented it but what happens is that after after the rows that are hidden it stops showing the rest that is unhidden. So for example rows 1 till 10 are visible, 10 till 50 hidden, 50 till 100 visible. But it only shows not 1 till 10.

[jaslake]

Hi Stignone

You're using Rows 10 to 50 for ListBox1...why are they hidden?

[Stingone]

Hi Stignone

You're using Rows 10 to 50 for ListBox1...why are they hidden?

Hi John in my example i uses D10:M500 so it's stops showing the rest of the rows that are visible after the first hidden rows.

[jaslake]

Hi Stignone

I don't understand.

The Code says this

Please Login or Register  to view this content.

But you indicated rows 10 till 50 are hidden

10 till 50 hidden

[Stingone]

Hi Stignone

I don't understand.

The Code says this

Please Login or Register  to view this content.

But you indicated rows 10 till 50 are hidden

Sorry John,

I used this code:

Please Login or Register  to view this content.

So somewhere on row for example 300 till 320 i have hidden rows. But it only show now till row 300 and not the rows after the 320

[jaslake]

Hi Stignone

Yes, that's the way it works with the line of code I provided. This sounds like a new Issue. Perhaps you should start a New Thread.

Another thing you should be aware of is that your Row Index will probably again be out of sync and, if you're updating records, the wrong record will get changed. Food for thought.