Userform - Search, Edit & Update

[nancyching1711]

Hi

I want to enhance usage of my userform to be able to search, edit and amend/update the data in the excel worksheet.

I am lost in between all the codes.

Kindly assist me with the following:
1) I want to search by "Identity No". Upon clicking the search button, all details will be populated in the userform for editing. However, I have a problem of populating the correct data when 1 person (with the sane identity number) has more than 1 record. Please assist me with the vba code for populating the userform after entering the date and identity number.

2) Upon editing, how can I update/amend the data update clicking the update command button?

Please refer to my attachment.

Thank you for the assistance.

Regards
Nancy

[ranman256]

it sounds like you are trying to do a lot of work you dont have to.
Cant you just FILTER the Identity# on the sheet then alter the results?

[nancyching1711]

it sounds like you are trying to do a lot of work you dont have to.
Cant you just FILTER the Identity# on the sheet then alter the results?

Hi. Thank you for the advice. My original worksheet has alot of data with many columns and various excel formula. Thus, it will be easier for users to use the userform for editing. Please assist to resolve. Thank you.

[mjr veverka]

1. 'Nullstring' is a reserved word in vba ('NullString' property), do not use it as a variable
2. Correct the form, some labels ("Effection Date", "End Date") have been covered by 'MultiPage'
3. Don't leave empty cells in place of 'No' - in the 'Vegetarian' column everything is filled out, and in others it is not, it makes a mess
4. Collect the search results to the area variable and display them in the controls of the form using, e.g.: "SpinButton" - see attachment (I have not looked at macros for data updates in sheet)

[bakerman2]

Another way.
Fill in Id No and click Search. Results appear in Listbox so you can see if there are multiple searchresults.
In Listbox click on item to be amended.
Make adjustments and click Update.

[nancyching1711]

Hi bakerman2

Your solution works great. However, I do not know how to apply to my original worksheet. Kindly explain the codes:

Please Login or Register  to view this content.

How do I interpret these numbers ie. 3, 1 & 11

Please Login or Register  to view this content.

What are the numbers denote?

Thank you for the great help.

Apologize for causing you so much trouble.

Regards
Nancy

[nancyching1711]

Hi Bakerman2

Thank you for the solution and I managed to figure out the codes to apply back to my original worksheet except for 1 item.

Please Login or Register  to view this content.

The date on my original worksheet is at Column G (The trial worksheet is at column A). I do not know how to amend the code so that the correct data will apply in the listbox.

Thank you for such a great solution.

Regards
Nancy

[nigelog]

try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

txtDate is getting the information from item 5 in the Listindex of the ListBox. These start at "0" so "5" if the 6th column which is "G"

[bakerman2]

When you go into the properties of the ListBox, look at the Columnwidths.
Every column you don't want to see is set to 0pts.
Remember that LB-Columns are 0-based so Column A = 0, Column B = 1 so Column G would be 6
If you have any other problems post an example file that reflects the true layout of your workbook.

[nancyching1711]

Hi

I amended.

However, after clicking the cmbSearch button, data /details in Column A is populated in the listbox.

Which code I have to amend as such that Column G will be populated in the listbox of the userform.

Thank you.

Nancy

[nigelog]

This section of the cmdsearch button is loading the columns in order

Please Login or Register  to view this content.

you need to amend this order of calling of the data to change the order of the listbox

[bakerman2]

If you put this in Listbox properties ColumnWidhts it will show you Column C and Column G.

Please Login or Register  to view this content.

[nancyching1711]

Hi

Almost there. Much appreciated.

One more problem; is the format of the date, both on the listbox and the userform.

Currently the date appeared on the userform txtdate and listbox differ from the worksheet. That is, txtdate & listbox is 1 Mar 2018 but the actual date on the worksheet is 3 Jan 2018

Thank you.

Regards
Nancy

[nigelog]

Please Login or Register  to view this content.

will correct the text box

not sure of the syntax for the listbox

[bakerman2]

I've fixed all the coding but for the date part it seems to be regional setting because on my part all displays well.
Try switching dd & mmm and check then.

[nancyching1711]

Hi

I do not understand "try switching dd & mmm". May I know what do I have to do?

Regards
Nancy

[bakerman2]

Instead of

Please Login or Register  to view this content.

try

Please Login or Register  to view this content.

[nancyching1711]

Hi

Thank you for all the help.

Regards
Nancy

[bakerman2]

Glad to help and thanks for rep+.