PDA

View Full Version : Calender control



PeterMC
07-02-2015, 06:38 PM
Hi, I have outlined the structure of what I am trying to make. I need to ask for some help. In the attached workbook, is the basic layout of the sheets that I am using.

The help that I need is: To make the calendar on the "Start" sheet show the quotes, the bookings unpaid and the bookings paid, in different colours, starting with the present month. And if I click on any one of them, the panel on the right will be populated with the booking details.

Can somebody please help with this. Thanks

SamT
07-02-2015, 09:24 PM
First, get rid of all those underscored acronyms and abbreviations and use real words. What the heck is D_P_R_B anyway?

UnMerge all Merged Ranges and use Horizontal Alignment = "Center Across Selection" instead.

On all Table or Database sheets, select Cell A2 and use the Menu >>Window >> Freeze Panes to keep the column header row from scrolling.

Then put enough valid information in all the sheets to cover all instances that belong to the three bookings in the Start sheet calendar, include at least one deposits returned. Assume that the current date is July 1.

I don't see any "Notes" columns. Or any Room Numbers.

You might want to think about adding "Planned Arrival" and "Planned Departure" to the Quotes or Bookings sheets. and leave the other two similar columns for the actual dates. Your choice.

Other than that, you have a real nice start of an effective Excel App.

After you do that upload the book again

PeterMC
07-03-2015, 07:29 PM
Thanks for your reply SamT,

I have taken note of your suggestions, please have a look through the workbook now and advise me.


The bookings will be made for specific dates and there will be a time before they can enter the prperty, probably 4pm, and departing before 11am. This will allow time for the cleaners to clean and prepare for the next guests, if it is the same day.

Regarding "room numbers", I guess I should explain a little about this project.

A local elderly couple have been running a help centre for deprived families. They are not a registered charity and do not have any assistance. They have a small shop, selling donated items and a large hall behind it, where they serve hot food and have an area for a local nurse to see people.

They are struggling to finance their efforts and myself and some friends have got together and refurbished the apartment, above the shop. The idea is for them to rent this and to boost their income, so that they can finance their centre.

I volunteered to make a basic "Booking system" to help organise the rentals for this apartment ("Silly me"). I have started with basic parts of this and I now need to ask for some help to get some of these parts working. I do not expect anyone to do everything for me, I want to do as much as I can.

As you can see from the workbook (Notes), I have made a lot of the system work but I am having problems getting the calendar to display the bookings and to be able to click on them and populate the panel.

So please help, if you can.

SamT
07-03-2015, 09:11 PM
I got the book, but I am running out of steam tonight. check back tomorrow.

PeterMC
07-03-2015, 09:40 PM
I got the book, but I am running out of steam tonight. check back tomorrow.

Thanks SamT, it's 06.40 here and I need to sleep a little also.

Thank you

PeterMC
07-05-2015, 05:08 AM
Update.

I now have the "Quote" system working and the "Booking" system working, upto the deposit payment. I will next work on the ·Rental· paid system, which will be much easier as most of the booking information is already stored. I can now change the cells (On the info panel) that add or view deopsit and rent payments. I also have the cell (On the info panel) for View customer details working.

Please, anyone can help with the calendar part, to display the quotes period pending payment of deposit, the bookings period where deposit has been paid and the bookings period where rent has been paid, with thos cells linking to a macro that will bring the booking details into the info panel.... PLEASE.......

PeterMC
07-07-2015, 01:45 AM
Any ideas out there? Please

SamT
07-07-2015, 07:09 PM
the first this is Completely describe the calendar table in VBA using Constants Variables and functions.

for Example, the Table itself

Public Const CalendarTableAddress As String = "$B$5:$V$14"

Or

Public CalendarTableRng As Range

Sub SetCalendarTableRange()
Set CalendarTableRng = Sheets("Whatever").Range("$B$5:$V$14")
End Sub

Your calendar has 210 cells across 10 Rows to allow for two full months. Looking at the July August calendar on sheet Start Notes 1 the first used Day is Wednesday, which makes the FirstUsedDateCell of the Calendar Range Cell # 7


Public FirstUsedDateCell As Long

Sub SetFirstUsedDateCell()
FirstUsedDateCell = (Day(1st of Month As Date) * 3) - 2
End Sub

The 1st table cell of any given date is

Public Function FirstCellOfDate(Dte As Date) As Long
FirstCellOfDate = (Day(Date) * 3) - 2
End Function


See the attachment here (http://www.vbaexpress.com/forum/showthread.php?53037-Colour-Entire-Row-Base-on-the-Value-of-Two-Other-Cells&p=327662&viewfull=1#post327662) for a logical way to describe a workbook, sheet or table

PeterMC
07-08-2015, 12:58 PM
Very interesting SamT, Thanks for the direction. Lots more reading and learning to do now.

Thank You