Populating forms with new or existing data and saving to a data sheet.

Need to look into that. It's become quite some battlefield with all these versions and pretty confusing (for me that is)
I will make up some overview for us so we can see where we are and decide what's to be done yet.
OK?

Tsjallie

Found this white box in my copy that I can't get to go away (In red box)!

Screen Shot 2015-09-24 at 8.14.18 AM.png

Just a note: i am using Windows 10 and PC Excel 2016!

u3rick

Weird thing that white box. Is always there when you have the workbook open?

As I mentioned when I uploaded the latest version (2.4g) I had some bad luck.
Sometimes Excel just stops working. When you're it recovers by itself. If not - like me yesterday - you lose a lot of work.
Looking into the 2.4g version to see if inactive units can be hidden, I see that I lost quite some more work than I thought

That's mainly about the formulas collecting the units and the formulas collecting the stays.
So I need to make these changes again and will try the also hide the inactive units.
Though I still think that only inactive units with no stays within the calendar should be hidden. That could be the case when a unit is rented and after that made unavailable.
Of course units would disappear when the stays run off the calendar.

Here's the workbook with the lost changes in the calendar.
Inactive units are now hidden.

What if you delete the textbox with to-do's on the Calendar sheet and the Admin sheet.

Got my Mac Mini working

You call this "mini"?

will the non-active units make the bottom blank line to big as there are more and more non-active units??

No that's just the size of the table. You can simply delete these rows.
You can then make an event procedure in the Properties sheet to keep the number of rows in the Calendar table equal to the number of properties which are active.

Of course I will have many questions before then

That's what this forum is for. I propose with this thread we go for a forum record in number of posts ;-)

BTW, I noticed a flaw in the formula displaying the active units on the calendar (column A). Haven't got the k-parameter of the SMALL-function right.
I'll figure that out and post a corrected version of the formula later.

Here's the corrected formula for the Property column (A) in the Calendar sheet:

Formula:

Please Login or Register  to view this content.

Now it will also work with other numbers of inactive units.

have a few beer it is Fri

Working on that

Tsjallie

None of the guest forms come up on the Calendar anymore?

Update: After restarting windows the forms came back. Not sure where they go, but I have to reboot windows to bring them back.

u3rick

Tsjallie

I am thinking I need to add units and owners, as well as editing units and owners from the ManagePropertiesAndOwners form. May need a check box for new owners and or new units? What do you think?

u3rick

Sorry, forgot to mention that it's an array formula.
Close it with [Ctrl][Shift][Enter]

I think you best integrate the AddNewOwner and AddNewUnit forms into the ManagePropertiesAndOwners form

Tsjallie

I can't seem to come up with the same page type you did on the form ManagerPropertiesAndOwners. I added pages on Property and it worked fine. But wanted to add two pages on Owners Tab and I can't match yours. I am attaching the new version could you set me two new pages in Owner section and send back?

Update: I figured out how to copy it so disregard this post!!

Thanks

u3rick

"No cells were found" error.
From the empty Owner(s) box I take it that for this unit no owner is found, but that situation should be covered by checking the number of found rows. If zero the line giving this error would be skipped.
Can you put a break on the GetPropertyOwners sub and then step through that sub with F8 while following what happens with the PropertyOwners table.
Code line of interest would be:

Please Login or Register  to view this content.

Is there a filter active on the PropertyOwners table when arrived at the line marked with A?
Are all rows in the PropertyOwners table visible after the line marked with B is executed?
Are there any rows visible in the table after the line marked with C is executed?
Is PropertyId in the second column of the PropertyOwners table?

PropertyID is Column A

That means it doesn't find the property.We are talking abt the PropertOwners table, I hope?
Then that's one part of the problem. The table is filtered on column B (field:=2) because that's where the PropertyId is in the version I uploaded (ES Program Redesign_Ricks_v2.3h.xlsm).
Can make that more flexible (not using column number).

It goes to the Datepicker

How do you start the ManagePropertiesAndOwner form. It should be started by right-clicking in the Property column of the Calendar.

Tsjallie

Nothing happens when I right click on a unit in the property column?

Copied all the coding from Tsjallie_Calendar Sheet in V2.3h and it at least makes an error message!

Screen Shot 2015-09-26 at 6.37.28 PM.png

Debug goes here!

Screen Shot 2015-09-26 at 6.32.58 PM.png

u3rick

Tsjallie

I have added a page to each tab (Property and Owner) to handle new owners and new properties needing added. It is not coded yet, so take a look and see if you think it will work.

The error that is in the above post is still here in this version. I attached the workbook so you could test it if you wanted.

Update: Disregard this Attached File!

I've been going through the workbook you uploaded.
In the document attached are my findings.
I only looked briefly at the updated version you uploaded. But contains the same errors and more.

Additional info:
you need to declare the variable FormEventEnabled in Module1.

Please Login or Register  to view this content.

This variable is meant to prevent form events from being executed.
The application.enableevents cannot be used for that because that only affects worksheet events.

Also don't forget to check the names of event procedures linked to objects in the form.
E.g. the cboOwnerId_change() event needs to be renamed to cboOwnerIdEO(change().

Cheers!

How did you know I'm having a beer

Tsjallie

I have updated and formed a new version. I hope I have included the all your changes. I have deleted unneeded forms. It also brings up and fills the frmManagePropertiesAndOwners (except New Properties & New Owners). It doesn't show frmStayInfo and it won't fill in Date ranges highlighted on the calendar.

Please check it out and advise on needed changes and hoe to get the frmStayInfo working. I think we are still holding on the filled in Date ranges highlighted on the calendar filling in the dates??

Thanks

u3rick

It doesn't show frmStayInfo

I had disabled this form because I found it pretty annoying when working on the Calandar.
You can simpy enable it again by removing the "Exit sub" in the beginning of the Worksheet_SelectionChange event of the Calaendar sheet.

I think we are still holding on the filled in Date ranges highlighted on the calendar filling in the dates??

You need to help me out on this one. When a user selects cells the cells can hold an occupation, "N/A" or be empty and any combination thereof.
I'm not sure what should happen on which combination. In the attached document I'm listing the situations I could think of and stated what should be done - if obvious - or - if not obvious - make a suggestion of what could be done.
Can you take a look and let me know what you like to happen.

Each of us making our own changes in different versions of the workbook is pretty confusion. We shouldn´t do that. Changes get lost. For now I will post the changes needed and let you bring ´m into the workbook. So there will be one consistent version of the workbook. Following is based on ES Program Redesign_Ricks_v2.6.xlsm

The From and Until txt boxes in PropertyAvailability form turns white and you can't see the dates, not sure how to fix it!

This comes from the DatePicker taking the color from it's date box which signifies the type of day selected (workday (=white), weekend day (=grey) or holiday (=orange)). Disabled that.
You need to make the follwoing changes:

In the ManagePropertiesAndOwners form on the PropertyAvailability page change the name of the From- and the Until textboxes in dtbxFrom and dtbxUntil. This is needed to make the DatePicker use these textboxes as databoxes.

Add this function to mdlDatePicker:

Please Login or Register  to view this content.

and in the codemodule of frmDatePicker replace the function Function CallingControl by this

Please Login or Register  to view this content.

and finaly in the codemodule of frmDataPicker replace the sub cmdAccept_Click by this

Please Login or Register  to view this content.

The PropertyAvailability form needs to have a save button added and a way to record it some where. What was your suggested direction on that?But that button is already there. It only needs code added to it which is executed when the button is clicked.The ManagePropertiesAndOwners form needs an adjustment so it can be brought up to edit all units even if they are not on the calendar?

There is a workaround for that you could use: when you bring up the ManagePropertiesAndOwners form for any unit, you could then select the owners tab. There you can select the owner of an unavailable unit and go back to the property details tab. There you make the unit available.

Maybe a button and a case where it is blank if the button brings it up with a combobox with all properties?

That would mean having empty rows in the calendar (or at least one) for the unavailable units. Alternatevely we could substitute the property code text box with a combobox so you can select an other unit instead of the unit right-clicked.

Working on the selections in the Calendar yet.
Will also upload some code to keep the number of rows in the Calendar equal to the number of active units in the Properties table.

I am having a couple of problems. I can't get the correct info into the Rate Section Rent and SecDep boxes. Could you check the code for me and let me know the error of my ways?

Two things:
Firstly: in the cboPropIdEU_Click procedure for the Rate and SecDep you are referencing the wrong columns. Should be O and P.
Secondly: better to call (and reuse) the FillPropertyDetailsPage, FillPropertyAvailabilityPage and the OwnerDetailsPage procudures. Code-once-use-many! So highly recommended.
But the latter means adjusting these procedures to either have PropCode or PropId as parameter. Will get back on that.

I am also having no luck populating the lstPropertyOwners box.

The line checking whether the propertyid exists uses the variable PropId which is not present.

Please Login or Register  to view this content.

As this will always be false, the part filling the lstPropertyOwners will never be executed resulting in an empty list.

I tested the form from the Owner Tab page and when you click on a new owner it populates both pages correctly I think! See if you agree?

Yep. Looks OK

Not sure on code to lookup unattached properties

You can add this sub to the codemodule of the ManagePropertiesAndOwners form and call it from the Userform_Initialize procedure.
Note that in the last version of the workbook you posted there are no unowned properties. So just delete one or more rows from the PropertyOwners table to see the list getting filled.

Please Login or Register  to view this content.

This code will keep the number of rows in the Calendar equal to the number of active properties.
Procedure will be executed each time a cell in the Active column of the properties table changes.
Copy into the code module of sheet "Properties".

Please Login or Register  to view this content.

That filtering and finding data seems very difficult to me

I noticed that lately I need to make a lot of corrections. More than earlier on.
I think that's a symptom of you strugling to keep up and process it all.
That's pretty normal (been there too), but it might also be demotivating. And that would be a waste.
I strongly recommend you focus on finishing the Calendar and it's functionality for now. Parking the financial part and others for the time being.
Then invest some time in going through the whole thing until you fully understand what's going on and why. Will help you with that of course.
I'm pretty sure that won't take you too much time as you're obviously eager to learn and have shown to pick up thing pretty fast.
If you commit yourself to this effort you will most likely be more self propelled after that.
And in the meantime your users can play with the Calendar.

Have been going through the workbook but I can't post the code due to some new(?) security regime.
Need to figure out how to work around that.

Or somthing I did?

No, it apparently has something to do with the number of code lines I tried to post.

These are the changes to be made.

Sheet Tsjallie_Calendar
Changed formula in the Calendar region (D4:AA27) into:

Formula:

Please Login or Register  to view this content.

This formula will show when a property is not available in a certein period (if not occupied).

Added Worksheet_Activate() procedure disabling direct cell editing.
Added Worksheet_Deactivate() procedure enabling direct cell editing.
As the whole calendar is driven by formula it should be prevented that cells get edited manually.

Changed the Worksheet_BeforeRightclick() procedure to handle selections in accordance to the specifications in CalendarSelectionsHandlingRick.docx
Copy the code from the attached workbook, because it apparently is too long to post here (gives a security error).

Added Worksheet_BeforeDoubleClick() procedure to cancel any double-clicks

Sheet Tables
Added table Non-availability (tblPropertyAvailability).
This table allows for properties to be unavailable for any months each year or for specific periods

Thisworkbook.Workbook_Open() procedure
Remove Application.Onkey statement

Form frmPropertyAvailablity
Added cmbProperty_Change() to call the FillPropertyAvailabilityPage function.

Please Login or Register  to view this content.

Added function FillPropertyAvailabilityPage to get the availability data of a given Property.
Copy the code from the codemodule of the frmPropertyAvailablity form in the attached workbook

Added the EmphasizeIfSelected procedure to emphasize checked unavialable months in the frmPropertyAvailability.

Please Login or Register  to view this content.

Changes are based on the workbook ES Program Redesign_Ricks_v2.6.02.xlsm.
To be complete the worbook containing all the changes is attached.

Rick,
what's the difference between the GuestStays table and the ListStaysInfo table?

Well, that means that there are more than one procedures called Worksheet_SelectionChange in the code module of frmTsjallie_Calendar.
Probably there also is an empty one which you should remove.

Tsjallie

Did you think on the code change to get (ManagePropertiesAndOwners) Property Detail Owner(s) listbox to change to cboBox choice? The attached version has your latest changes as well as mine.

Thanks

u3rick

Screen Shot 2015-10-06 at 6.28.01 AM.png

Did you think on the code change to get (ManagePropertiesAndOwners) Property Detail Owner(s) listbox to change to cboBox choice?

Not sure. Will check tonight.

More changes :)
frmGuestActivity
In the cboGuestListGI_Change() procedure replace these lines of code

Please Login or Register  to view this content.

with these lines

Please Login or Register  to view this content.

And also replace these lines of code

Please Login or Register  to view this content.

with these lines

Please Login or Register  to view this content.

Ok, but then you must promise me not to make any changes until I uploaded a new version with these changes. Won't take days.
Go wreck some boats in the meantime if you get bored

New version. Think we are in sync now.

frmManagePropertiesAndOwners
- Changed sub cboOwnerIdEO_Change()
- Replaced sub cboPropIdEU_Click() with cboPropIdEU_Change()
- Changed sub Userform_Initialize()
- Replaced sub FillPropertyDetailsPage() with function FillPropertyDetailsPage()
- Replaced sub GetPropertyOwners() with function GetPropertyOwners()
- Replaced sub FillPropertyAvailabilityPage() with function FillPropertyAvailabilityPage()
- Replaced sub FillPropertyOwnerDetailsPage() with function FillPropertyOwnerDetailsPage()

frmGuestActivity
- Disabled sub cmdRecPaySDPSD_Click() ... called an non-existing userform
- Disabled sub cmdRecRefR_Click() ... called an non-existing userform
- Disabled sub cmdRecRefSDRSD_Click() ... called an non-existing userform
- Changed cboGuestListGI_Change()
- Removed sub cboGuestListGI_Click() ... obsolete

Sheet Tables - tblPropertyAvailability
- Added column PropertyId

Would be easer if we were using something like DropBox so we worked on the same file!

Will think about that. Would need to find a way of working together without minimizing this thread's value to the forum.
Don't know how it's on your end, but have received some spontaneous reps for this thread.
So apparently we are doing something other people like.

Not sure about that.
frmPropertyAvailability is a duplicate and already present in frmManagePropertiesAndOwners. And I think it belongs there because of it's affinity to Property.
I rather think there should be a form like frmManagePropertiesAndOwners for the Guests and GuestStays. I think frmGuestActivity should be moved towards that.
Having one form to handle all the Property and Owner related stuff and one the handle all the Guest and Stay stuff would make sense I think.

I really like both, is that a problem

It's not a problem. It just means that you will have to make changes to the Availability form (if any) at two places.
At the moment it's implemented to work with both forms (as you specified). Unfortunately you already removed frmPropertyAvailability. Just copy it back from a previous version and it'll work fine.

Am I not using them correctly because neither Blocking page works to block dates. How do I make them work, where are we tracking blocked dates?

That part of the Calendar formula is referring to the wrong column.
For instance this is the relevant part of the formula in D4:

Formula:

Please Login or Register  to view this content.

Can you check the GuestStays Sheet. The Darker green columns are formulas in the cells. Column G won't keep the chosen date format with added rows (should be m/d/yyyy, but new lines are d/m/yyyy). Also when a new row is added column H & I are left blank with no formula!

You're experiencing some weird "feature" of Excel tables. Sometimes old formats and/or old formulas keep linguring on "below the surface". Probably caused by some inconsistency in the past, but not sure.
Found an article on this issue some time ago, but can't find it now (of course).

Solution however is to copy to whole table and immediately paste it back again. This will force Excel to rewrite the table section in the xml the workbook consists of.

To view the xml just rename the workbook to .zip and open it using windows explorer. You will then see a map called "xl" which contains a submap called "tables". This map contains the xml files for all the table in the workbook. You can open these xml files which contain the formulas.

I didn't post the whole formula, only the last part. If you just copied that the formula won't work.

code to the third page of StayAcitivity form is that causing a problem with the ownerproperty form?

Not sure what you mean

I really like both, is that a problem. They have different times to be used

I'm not so sharp at the moment. Caught the worst cold in years.
You're right in this. It indeed is better to have the StaysEntry and NewGuest on one multipage form.
For the PropertyAvailability you can either choose to have a separate form showing when a cell with "N/A" is right-clicked or you can show the ManagePropertiesAndOwners form with the PropertyAvailability tab selected.

Like I mentioned I,m not very sharp at the moment. And it's even worse than I thought
But I think the coin finally dropped.
Forget about the previous post (except #84) and let's restart from post #78.

I will take a look at the PropertyAvailability form. I believe there's just some code missing yet.
The blocking data should go in the PropertyAvailibility table on the Tables sheet. That's where the formula in the Calendar reads the availability from.

PropertyAvailabilityTabe.JPG

BTW, I've sent you a PM about Dropbox

What about changes I made in post #82?

I will check on them.
Will make the changes to read and store the availabilities tonight and explain 'm.

Here's the workbook with the changes so far. Didn't finish yet. Ended up in bed with an aspirine.
Worked on the Availability page in StayActivity. Checkboxes working, adding new periods working. Working on deleting them yet.
Availabilities appear and disappear in the Calendar right on.
Will make up a document with explaination later this weekend.
Posting this version so you can move on.

As far as I can see on the screenshot the periods blocked are nicely on the calendar.
But I assume you want the selected months pre-added to the From and Until boxes. That's just not there yet, but can be done easily.

And yes, beer always helps

Tsjallie

Well the problem is it added to 2015 dates at the same time it was only suppose to do 2016 dates.

Update: Seemed to work in 2.7.03a but really didn't change anything that should affect it. If I did, I didn't know I did!

Thanks

u3rick

Let's just take this version as our base from now on and move the other versions to some safe place far away.

To post #97:

In the StoreAdhocAvailability procedure the function FillPropertyAvailabilityPage was called with the parameter txtPropertyCode.
This parameter, however, contains the propertyid instead of the propertycode. The procedure repopulating the list cannot find the propertyid in the propertycode column and so cannot populate the list.
I changed it to use the propertycode from the PropertyDetails page, txtUnitCodeEU.
Smae goes for the RemoveAdhocAvailability procedure.

It's not selecting July, because the EndDate is 6/1/2017.

Also made a change to the formula in the Calander:
Changed

Formula:

Please Login or Register  to view this content.

into

Formula:

Please Login or Register  to view this content.

This is to allow for StartDate not being the first day of the month.

That indeed would make the PropertyAvailability page in frmManagePropertiesAndOwners quite different from the page in frmStayActivity.
And so they are no longer duplicates. Good going!

Tsjallie

I had the From And Until populating with the picked months from the Calendar in version 2.7.02 but can't find the code or where it is even located. Give me some help, what am I missing? I erased the months from the form so I am getting closer. Everything is working well and once I get this done, I can go into some major testing by making a live version and using it daily with actual Vacationville activity!

Thanks

Sorry still cry from Hawks big loss today!

u3rick

Sorry still cry from Hawks big loss today!

I know how you must feel. Saw our national soccer team (recently fifa world #1) struggle to overcome fifa world #142 (Kazachstan) last saturday
Makes you wonna howl over a pint of beer

In the Worksheet_BeforeRightclick() procedure of the Calendar find these lines:

Please Login or Register  to view this content.

and remove the apostrophes.
Sorry, forgot to remove these when I was testing for the Add-button to be disabled when the data boxes are empty.

Change the cmdDeleteBP_Click() procedure in the frmStayActivity code module

Please Login or Register  to view this content.

This will disable the Delete-button again after a period was deleted.

When removing the checkboxes from the PropertyAvailability page of frmStayActivity, make sure you also remove any code related to these checkboxes.
This means that in the frmStayActivity the following procedures can go:
- StoreMonthlyAvailability
- RemoveMonthlyAvailability
- EmphasizeIfSelected
and from the FillPropertyAvailabilityPage procedure these lines should be deleted:

Please Login or Register  to view this content.

Tsjallie

Made the changes in post #103. I never looked at the Calendar code

In the frmManagePropertiesAndOwners in the Property Availability tab is the "Property is permanently unavailable" checkbox suppose to work? What does it do?

Also the "Save" button at the bottom of the page?

Thanks and have a great afternoon!

u3rick

In the frmManagePropertiesAndOwners in the Property Availability tab is the "Property is permanently unavailable" checkbox suppose to work? What does it do?

It should set Active to "Yes" or "No" in the Properties table.

Also the "Save" button at the bottom of the page?

All changes made are immediately stored. So the Save-button has become obsolete.

When you arrive at that point the Calendar is the activesheet not the Owners sheet.
In that case you get the error "subscript-out-of-range".
So change activesheet.listobjects("tblOwners") into Worksheets("Owners").listobjects("tblOwners").

Furthermore it's recommended not to use the range-objects with hard references to columns like in ws.Range("B" & linrng).Value = txtLastNameEO.Value.
But use the listobject with references to column names instead.

Clicking the Save-button executes the cmdSaveEO_Click() procedure.
When the line

Please Login or Register  to view this content.

is executed, this triggers a whole chain of events among which is the FillPropertyOwnerDetailsPage procedure which read the data from the Owners table and puts them in the PropertyOwners page. See 'm coming?
This means that the data you just changed is overwritten by the original data in the table. So nothing changes.
This chain of events triggered also makes that saving data is awfully slow.
Just set a breakpoint at sub cmdSaveEO_Click() and from there step through the code by pressing F8. You'll see what happens.

The solve this:
in the cmdSaveEO_Click procedure insert right after the DIM-statements the line:

Please Login or Register  to view this content.

and right before End Sub:

Please Login or Register  to view this content.

This will prevents this chain of events being triggered.

Tsjallie

Wow! I would have never figured that out! Now it works great.

Furthermore it's recommended not to use the range-objects with hard references to columns like in ws.Range("B" & linrng).Value = txtLastNameEO.Value.
But use the listobject with references to column names instead.

The listobject is tblOwners and a Column name is "OLastName" but how do I format that instead of ws.range("B" & linrng).value. and what do I do about

Set ws = ThisWorkbook.Sheets("Owners")
linrng = Application.Match(cboOwnerIdEO.List(cboOwnerIdEO.ListIndex, 0), Sheets("Owners").Columns(1), 0)

If IsError(linrng) Then
MsgBox "Id Number not found"
Else

Thanks, would be lost without your help!

u3rick

It posted twice and will not let me delete sorry!

This is how you can update the Owner details using the listobject with column names isstead of hard cell references.

Please Login or Register  to view this content.

Finally got Notifybox to work
So we now should be able to work on the same file together without interfering.