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
Hi Stingone
This code is in the attached...let me know of issues.
Please Login or Register to view this content.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
When testing it gives an error "cant find library in Private Sub UserForm_Initialize()Originally Posted by jaslake
Hi Stingone
This code is in the attached...let me know of issues.Please Login or Register to view this content.
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
Last edited by Stingone; 11-05-2012 at 04:12 PM.
Hi Stingone
Yes, the code as written will NOT update the ListBox...only the Sheet. Replace the code with this...it also updates the ListBoxPlease Login or Register to view this content.
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
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.
Hi Stingone
Ah...I see said the Blind Man. Replace the CommandButton2_Click code with thisLet me know of issues.Please Login or Register to view this content.
Great you are the best
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 codePlease Login or Register to view this content.
Last edited by jaslake; 11-07-2012 at 11:43 AM.
How can i show a specific part of the worksheet in the listbox instead of the whole worksheet?
Last edited by Stingone; 12-02-2012 at 12:40 PM.
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 ?
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?
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?
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.is it possible to get in every multipage a different listbox input to change?
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
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.xlsPlease Login or Register to view this content.
So im almost there, but now only the right update and only the range D1:L19 instead of whole sheet
Last edited by Stingone; 12-04-2012 at 05:59 AM.
Gegevens veranderen.xls Here another example that's not working with a range.
Hi Stignone
Change your ComandButton4 code to thisPlease 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?Hi Stignone
Change your ComandButton4 code to thisPlease Login or Register to view this content.
Please Login or Register to view this content.
Last edited by Stingone; 12-04-2012 at 02:17 PM.
Hi Stignone
This makes no sense to me in relationship to your posted fileExplain what it is you're trying to do...I don't follow.Can you maybe provide the code to change the range for example D10:M50 instead of UsedRange?
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
Last edited by Stingone; 12-04-2012 at 02:33 PM.
Hi Stignone
I assume you tried your revised code...what happens?
It works fine until i try to specify the range. and change the collumns etc.
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.
Hi Stignone
What error message are you getting?
Couple of error e.g. cant find list property very large error in numbers. you see it when opening the attachment
Hi Stignone
See if this helps.
This should work using your example. But when clicking on the update button nothing happenPlease Login or Register to view this content.
This should work using your example. But again getting error 381Please Login or Register to view this content.
Last edited by Stingone; 12-04-2012 at 04:30 PM.
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?
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
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
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 Codenone 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.Please Login or Register to view this content.
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.
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
http://www.excelforum.com/excel-prog...55#post3034455
Hi Stignone
In the UserForm Initialize Event, for each ListBox, change this line of codePlease Login or Register to view this content.
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.
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.
Hi Stignone
I don't understand.
The Code says thisBut you indicated rows 10 till 50 are hiddenPlease Login or Register to view this content.10 till 50 hidden
Sorry John,Hi Stignone
I don't understand.
The Code says thisBut you indicated rows 10 till 50 are hiddenPlease Login or Register to view this content.
I used this code: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 320Please Login or Register to view this content.
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.
Last edited by jaslake; 12-06-2012 at 03:31 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks