Userform Question

[healey21]

I am looking to use a Excel VBA UserForm and with the form I am adding a person name, a product they sell and date of sales.

What I am doing currently is using a worksheet for that person and then using cell A2 to hold the Product and cell D2 to hold the date of sale, and subsequent A3 for the next Product and D3 for the next sales date. I need the form to check the sheet name against the sales person and then go to A2 find the next empty row and add the product and sales date. Then check the Master sheet for the sales persons name and find the product and add the date.

what I then do on the Master sheet is to have all my sales people down column A and the sales products go across Row 1, what I would like to do is when using the form, Add the date to the master for the sale of the product when I am selecting to add a Sales person and Product.

My userForm has 3 options txtSales, cboProduct and txtSalesDate

On the Master Sheet All the products are listed across Row 1 and and sales persons name in Column A (starting in A2), I would like to capture the date of sales where it finds the sales person and product which has been selected.

The workbook currently has multiple sheets for each sales person and I would like to run a routine to go through each sheet, finding the sales person name and add the date for each product if possible. Each sheet is laid out the same and a sample is indicated below:

Sample of Sales Person worksheet

Excel Workbook
	A	B	C	D
1		Sales Person Name	Graham Smith
2	Product	Sales Value	Amount Sold	Date of Sales
3	Balls	12	32	01/02/2011
4	Phones	34	123	02/02/2011
5	Chairs	45	5	30/03/2011
Graham Smith

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Sample of Master Sheet.

Excel Workbook
	A	B	C	D	E	F	G
1		Balls	Phones	Chairs	Knifes	Ink	Stationery
2	Sales Person
3	Graham Smith
4	Alan Brown
5	Julie Jackson
Master

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Sample of Master sheet with dates shown for Products

Excel Workbook
	A	B	C	D	E	F	G
1		Balls	Phones	Chairs	Knifes	Ink	Stationery
2	Sales Person
3	Graham Smith	01/02/2011	02/02/2011	30/03/2011
4	Alan Brown		01/02/2011	02/02/2011	01/03/2011
5	Julie Jackson				01/02/2011		01/03/2011
6						02/02/2011
Master

 

[Trevor G]

Hi Healey21

From your user form you can add a command button that will add the product and sales date to the sheet for the sales person. You can look to add a Do Until for this, take a look at this example below:

Range("A2").select
Do Until Activecell.value=""
activecell.offset(1,0).select
Loop
activecell.value=cboProduct.value
activecell.(offset,0,3).value=txtSalesDate.value

This should help with using the form, as I assume you will activate the sales person sheet when the form is initialized or activated.

 

[healey21]

Thank you Trevor G,

I will look to test this and reply accordingly.

Can anyone help about the Summary option.

 

[healey21]

I have used the code you suggested Trevor G and it works for the worksheet I work on for the sales person but now need to get it to check the Summary sheet and place the date in against the Product Sold any help will be wonderful, if someone can assist.