userform emailing from... receiving to... matching up and more...

[chuf]

Hi All,

Following on from the relative success of my (your) last userform …. another team has asked for one to “transform” their team’s room booking system. I did suggest this was not my job, I just offered once… long, long ago … so they agreed it didn’t need to be done before Christmas !!!!

As part of my initial scoping I’d really appreciate some advice, hints or tips on the layout of the form. I've no idea where to start really, to be honest

The system needs to works a bit like..

A client wants to book a room ( but sometimes a number of rooms ) they will provide the dates of booking, specific room needed and contact details.

The supplier will receive a verbal order and will complete the userform with the details of the booking, their code to be credited and the amount to be charged. This needs to generate a unique booking reference with a prefix depending on the client team name.

The supplier will need to send a summary of the order (I’m assuming a worksheet) via email to the client for them to confirm details and authorise the charge.

The client will then enter the financial code to be charged and complete / mark / update ithe order as confirmed correct and return it to the supplier.

I’m quite sure this will need a multipage userform… as it will be too unwieldy for one sheet.

So to the questions…

1. I’m really confused about the best way to generate summary information from the userform, send it via email and then when received back.. to match it up ?

2. If it was just one room booking per form that would be “easier” but sometimes there’s up to 10 so there’ll need to be a way on the userform of adding more sections for more bookings. This can all remain under the one unique reference but the dates, room name and financial code returned from the client will differ.

This is just my first thinking around this.. I sense another trial.. but I learnt such a lot from the last one... the basic setting-up is ok… its just these initial additional fiddly bits.

Really appreciate any thoughts,

So, so confused. Chuf

 

[chuf]

Hi Board,

I know the board hates waffly non-questions but i'm going to do it again.. taking number 1. from my waffle below..

1. I’m really confused about the best way to generate summary information from the userform, send it via email and then when received back.. to match it up.

Where would be the best place to start on this ?

i'm thinking of something like.. posting the detail from the useform to 2 w/s ... one which will be the main record and one which will be a temporary w/s to be emailed off.. the tab name = to the booking reference then when this is completed, emailed back and opened by the supplier department who will have the main w/s , which will also contain the booking reference, will be able to link the details together ( i've seen a few codes for this sort of thing on here).

Am i going along the right sort of lines with this ? really appreciate some ideas or at least tell me mine is rubbish

Thanks all. Chuf

 

[chuf]

Ok all….

I’ve broken down my latest quest into something more tangible… and it seems like a right rotter to me.. in fact its getting more of a rotter as I type.

I’ll have a MasterSheet populated via a userform. This MasterSheet will look a bit like as below..

I need a vba code to be able to …
Pick up all rows not actioned previously, i.e. those without a Y in column D.
Create a new workbook for all the email addresses – maybe called “Bookings janedoe and the date “
With each line relevant to the user being a worksheet with the tabname equalling the Order name in column B..
In the example below.. jane.doe would have one workbook, with two worksheets, one called A132 and the other A789.
A range of columns from the MasterSheet would be shown, so would be fab is the code shown (easily) which columns are deleted so I can adapt.
Once the code is run a Y would need to be added to column D to ensure its not run again.
This will be emailed off.

Then once returned I will need the supplier to be able to open the workbook that contains the MasterSheet and the workbook with the tabnames and to be able to pick up the details entered in column I. I’m going to need to get more detailed info on this post Xmas.. as the user just wants a ropey prototype ( thank the Lord cos I only do ropey )

Be fab if anyone can assist … Chuf

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Team	Order	email address	Order issued	Detail 1	Detail 2	Detail 3	Detail 4	Detail 5	Detail 6
2	Team A	A132	jane.doe		abc1	ghi1	jkl1	mno1	will be filled in	stu1
3	Team A	A123	charlie.brown		abc2	ghi2	jkl2	mno2	and returned	stu2
4	Team A	A789	jane.doe		abc3	ghi3	jkl3	mno3	will need to be	stu3
5	Team B	B456	joe.blogs	Y	abc4	ghi4	jkl4	mno4	matched	stu4
6	Team C	C753	an.other	Y	abc5	ghi5	jkl5	mno5	up with	stu5
7	Team B	B489	joe.blogs		abc6	ghi6	jkl6	mno6	MasterSheet	stu6
8	Team D	D751	andan.other		abc7	ghi7	jkl7	mno7	on return	stu7
9		Tab Name	Send To	Ignore if Y	Copy	Copy	Don't copy	Don't copy	To be complete	Copy
Testing