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

chuf

Well-known Member
Joined
Jun 12, 2006
Messages
619
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 :LOL: !!!!

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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

chuf

Well-known Member
Joined
Jun 12, 2006
Messages
619
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

Well-known Member
Joined
Jun 12, 2006
Messages
619
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. :confused:

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
ABCDEFGHIJ
1TeamOrderemail addressOrder issuedDetail 1Detail 2Detail 3Detail 4Detail 5Detail 6
2Team AA132jane.doeabc1ghi1jkl1mno1will be filled instu1
3Team AA123charlie.brownabc2ghi2jkl2mno2and returnedstu2
4Team AA789jane.doeabc3ghi3jkl3mno3will need to bestu3
5Team BB456joe.blogsYabc4ghi4jkl4mno4matchedstu4
6Team CC753an.otherYabc5ghi5jkl5mno5up withstu5
7Team BB489joe.blogsabc6ghi6jkl6mno6MasterSheetstu6
8Team DD751andan.otherabc7ghi7jkl7mno7on returnstu7
9Tab NameSend ToIgnore if YCopyCopyDon't copyDon't copyTo be completeCopy
Testing
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top