Copying random columns from one sheet to another in same workbook..Problem?

Attachment 398569Hi All

This is always challenging for us beginners. I have written some code that works and accomplish about 90% of what I need but the the last 10% is the hardest and I am stuck! So here is what I am trying to do. I have three logs (same workbook) that get copied to every time there is a new owner and unit added to the rental program. One log for owners one for units and one that is a combination of each (some owners own multiple units) The OwnerPropLog is where i attach the ownerId to the systemId and Unit Id. I know it confuses me too! These logs will be locked and hidden from the program users. That is just some background so you have an idea of what I am doing. Now the problem. I have developed some code to build a sheet with a list of owners and units that will be visible to the users. I pulled columns from the three sheets to make the Owner sheet. here is the code.

Please Login or Register  to view this content.

Now the problem, there is the fact that existing owners my buy additional units. When this happens there is no entry from the OwnerLog because there is no new info. That is ok because I only want the unit listed anyway. The problem is that the next entry fills the blanks cells left from the existing owner entry, because the code is looking for the first empty cell at the end of the row. My next move was to enter it by cell so I could enter a NA in the blanks but have no idea how to do that. So I am stuck and hoping for some guidance. The above code most likely is not so good so help there would also be appreciated. I included the file if you need it.

Thanks, hope I made this so you can understand?

u3rick

The workbook you posted is loaded with personal data of the owners (your customers?).
Please remove it asap and replace it with a sample without these personal data.

Further I see your code is using the same object and variable for all sheets and lastrows.
Be aware that only the last assigned value will count as the previously assigned value will be overwritten by the next assigment.

Will take a further look at yr issue.

Here's a workbook with the refreshing of the Owners sheet working "properly".
Apart from not copying the Owner data when a second property is held, also the Owner/Property combination got mixed up.
That's because the ownerPropLog holds duplicate Owner Id's when more than one property is held. And the copying
depends on the data in OwnerLog being in the same order as in OwnerPropLog, which with duplicates will never be the case.
It's now only is copying the Owner Id's from the OwnerPropLog sheet into the Owners sheet.
The other columns are filled with lookup formulas looking up the data based on the Owner Id's. This to avoid the mixing up
of Owner/Property combination when owner hold more than one property.

However, going through your workbook it's clear that it is still very sensitive to changes.
That is mainly caused by many hard coded cell and range references.
That can be dealt with of course, but in the way your workbook is set up, that would take a lot of coding.

I would strongly recommend you to reconsider the setup and make the following changes:
- Basically the core of your application needs 3 tables:

- One to hold the Owner data
- One to hold the Property data
- One to hold the Owner/Property combinations

In the current workbook the Owner/Property combinations are spread over two tables (Owners and OwnerPropLog) and these need to
be synchronized permanently. Integrating these two tables into one would render this need for synchronization obsolete.
Apart from these to 3 tables you can of course have additional tables for logging changes made by users, data validation etc etc

- You're spending a lot of code keeping track of range dimensions and finding where a range ends to insert new data.

Using Excel tables would make that a lot easier, because Excel would do all that for you.

I'm aware that this is just a lot of text. I'll try to make up a sample workbook the demonstrate what I mean.

This will mean I have to totally change all code

I rather think you will need to delete a lot of code instead of changing it. And there is a button for that
And yes, you is better to go through this so when you start developing the userforms you have a solid and reliable base to build on.
Welcome to programmer's life

I think I should mark this as solved

Depends how you look at it.
The problem you posted may be solved, but you're far from ready.
So as far as I'm concerned you may leave the thread open for a while.

Ok, here's a revised workbook.
Hope it won't come too much as a shock for you.
All the code is gone, because you won't need it anymore.
Rest assured, other code will come as soon as you're starting to bring the userform to live.

I merged the OwnerLog and OwnerPropLog into one table PropertyOwner.

Actually the workbook as it is now is already functional (apart from things like payments, refunds and alike):
you can add Owners, Properties and connect Owners and Properties.

Next things you need to do now:
- Check if all tables have the right columns. If not just move them (cut-and-paste).
- Start developing the userforms (the fun part).

You can read some basic things about Excel tables here.

BTW, thx for the rep (and I'm even not finished with you )

No need to find out where you need to insert the new row.
Using tables Excel does that job for you.

Please Login or Register  to view this content.

BTW, in this code is 1 column (#1 or #2) which is not in the table.
Make sure your code is in sync with the table layouts. So better to check that before you start coding; can save you an awfull lot of work.

I will look at the frmUnitEntry and add code to make it work.
Then you can use that as "template" for the other forms which are more of the same.
As I see you're picking up things pretty fast I think you caanhave this workbook up and running in no time

I am trying to make this program similar to the companies Vacation Reservation System

Is the workbook you're building intended to replace that system or will it just be an extension of it.
If the latter, will you be exchanging data between the system and your workbook.

Here's a new version with UnitEntry form working OK.

Commented out the messages popping up when skipping some textboxes.
If found that pretty annoying, but you can just remove the apostrophes if you like.

Changed City into a textbox, because there is no list the attach to it.

Changed State and Country to comboboxes and attached them to the related lists.

Replaced Userform_activate procedure (fires every time a userform becomes active)
with the Userform_initialize procedure (fires only once at loading the userform).
Used that for setting the rowsources of the comboboxes. And that needs to be done only once.
Alternatively you may set rowsources in the properties of the comboboxes if you like.
Personally I find it more convenient to have all these kind of things together in the initialize procedure.

Removed some obsolete code doing things already done elsewhere.

And finally changed the code for adding the data to the Properties table.

So actually I only made 2 changes that where really necessary.
I like the way you use the change and exit events for notifying omissions (apart from the message boxes that is )

Will get back to you about the design. Needs some explaination for that and a little diagram.
Champions League Final starts in half an hour and don't wonna miss that. Will post tomorrow. Ok?

Also I was wondering if I should just erase the Exit Sub in all those formatting function that you noted weren't recommended?

Don't just erase them. Would need some more adjustments.

Wow, I see you're making good progress!
Not many, but a few remarks:

• Delete GuestStays and rename Stays to GuestStays
• GuestStays (fna Stays) is missing GuestId
• It's recommended to put the Id's together in the left most columns of a table
• Why this table with OwnerId's and Lastname in the Tables sheet. There is already an Owners table.
• Risk of this is mixing up data which is already the case and you'll need to maintain data in duplicate.

I have a design question, do you think I need an accounting sheet for each Owner and each GuestStay or can that information be filtered and lookup from the payments table?

And I'm attaching a ppt (zipped) with some guidelines on when to create separate tables and the use of id's.
To answer your question: I would recommend to one accounting sheet for all owners together and use filtering to select accounting record for specific owners. Some goes for the GuestStays.

Looking thru the userforms I see an issue coming. That is when you want to display a recordset. For example Payments in frmGuestActivity.
Don't do that with all textboxes but use a listbox instead.

Calendar looks slick!
But better use

Formula:

Please Login or Register  to view this content.

for the months. So you don't need to specify the number of days in a month. Excel knows that.
And you could make such a calendar very dynamic so it will keep itself up-to-date.

To fill a combobox with OwnerId and Lastname using the existing Owner table you can use ths code:

Please Login or Register  to view this content.

Example applies to frmOwnerIAddUnit. Put the code in userform_initialize and you're done.
After that you can remove this extra table holding OwnerId and Lastname.

Can you upload the version of the workbook giving this error. Need to take a look under the hood.

Got the culprit! And it's you
You changed the range name "Owners" into "tblOwners".
If you change that in this line like this

Please Login or Register  to view this content.

and it'll work fine.

Did some work in ChangeLog Owners no working?

What's not working?

BTW, I would put the logging in worksheet_change events, so you don't have to repeat the code for that all over your project.
Alternatively you could write a macro taking some parameters which you could call whereever you want to log something.

About Payments:
Business perspective goes before programming perspective.
From both perspectives I would separate Owner and Guest transactions, but have all kinds of financial transactions together in one table.
So you would get one OwnerFinance table with dues, payments, refunds etc and one GuestFinance table with dues, payments, refund etc

About Login form
Whether you need a login form or not depends on your purpose for it.
Remember that users are already logged in to their system/network and you would need to maintain a user/password table in the workbook.
It would also for users be possible to exchange userids/passwords.
Better is to collect the userid from the system with Environ$("Username") which you can use to set a user's autorization and use the id in the changelog.
If you want to set autorizations it's best to define roles (like admin, power user, user, guest user or whatever needed) and a Users table in which you can assign roles to them.
I will post a example of such an autorization matrix, user table and some sample code later.
If it's just for the changelog no extra table needed.

What are yr company rules on that point. Let them be decisive.
I think the financial tables should be hidden anyway. Or actually all should be. Except for the admin.
Preferably these kind of data should only be visible for autorized users.
Good that I'm working on this autorization demo

And questions is what this forum is for. Keep asking them.

BTW: after next Friday I will be away for a two weeks vacation. So you will need to park 'm for that time. Or start another thread if it's urgent.

Funny difference between engish and dutch: we say "next Friday" where you would say "this Friday".
Of course the only right way to say it is the dutch way So the 12th it will be.

About Multipage
Hmm, overlooked that one. Sorry.
You're using the wrong object. mpgDetailOIAU is the 1st tab, not the multipage object.
All you need is

Please Login or Register  to view this content.

BTW, some of the forms are really big (height). Don't fit on my screen, which makes it difficult to run 'm.
Any chance you can make 'm smaller, say max. height 500?

Login and authorization
I promised you a demo for setting authorizations based on the username.

Tsjallie

Well of course and have a great vacation! I was just thinking about the form sizes yesterday, because I have two Apple ThunderBolts Screens (lots of space), Users have smaller screens. I will fix that and send you a new file soon. The new file will also include my first attempt at the Guest Transaction Table (GuestTrans & tblGuestTrans), see what you think. I didn't include any total columns in the table as I figured those would be better placed on the sheets made for the different output reports developed from the table.

Thanks

u3rick

Here it is

ES ProgramTest Redesign_v1d.xlsm

This listbox where you can choose a name is just for the demo.
In real it wouldn't be there. In the ThisWorkbook_Open procedure you will get the username from Environ$("Username"). So a user cannot work in the workbook with an other name.
Also no need for a login form. So no possibility for users to exchange id's and passwords. Works fine unless there are real hackers in the family.
From the ThisWorkbook_Open procedure remove this line:

Please Login or Register  to view this content.

and uncomment this line:

Please Login or Register  to view this content.

And don't forget to enter your username in the User table on the Admin sheet!
You can check the username Excel will read by entering the command

Please Login or Register  to view this content.

in the Immediate Window from the VBEditor.

About the form
Now they fit nicely. Must have cost you quite some hours of tedious labour

About GuestActivity form
I see you also replaced the textboxes for the payment in the guestactivity form by a listbox. That's much better!
Tip: if you're linking it to the Payments table you will need to change the column order of that table so the first four columns match the columns in the listbox.

About Payments table
Wow. Lot of data there. Will that be entered manualy or be imported?
GLname and GFname are not needed or they could be looked up from the the Guests table.
Same goes for the GuestId and GPropCode, these can be lookued up in the GuestStays stable with the StayId.
Would save some coding.

About listboxes and GLname (and alike)
I like the way you use separate tables for the list- and comboboxes.
However from a technical perspective it is good practice to store data once and use it as many times as you need. May (or rather will) save you a lot of debugging time later.
So having columns from a table re-appear in other tables is fine and prevents the need of nitting together data from several tables.
Just make sure that you use lookups for the duplicates.
For example: the GLName is already in the Guests table. So for the Payments table you'd better lookup the GLName in the Guests table with the GuestId.
Just put the lookup-formula in the Payments table and every time you add a row to that table the formula will automatically be copied.
Like I did for the PropertyOwners table. In that table only the columns A:E are filled with data. All the rest is coming in thru lookups.
Same goes for the tables you use for list- and comboxes.
However you need something extra there. If you would use a listbox table with - let's say - GuestId, Guest Lastname and Guest Firstname, you can have all the cells reference there related cell in the Guest table. But when you would add a new guest to the Guest table, that would not be automatically added the listbox table. For that you would need to add a row to that table.
You can automate that by having the Worksheet_Change event procedure of the Guests sheet adding that new row to the listbox table.

Yes, this a long story. I will try to upload a small example to demonstrate this later (edit: done that)

About Guest Activity form
Have been looking into this forms and anticipated that you may have a struggle to filter the payments for the guest.
And there are some nasty things to take into account with this.
Added a listbox to select a Guest and did some coding to demonstrate how to do this filtering in the code module of frmGuestActivity in ComboBox1_Change(). Comments to explain are in the code.

Tsjallie

Where do I set focus for the second Tab and txtbox? The following code runs without errors but does nothing to the focus or Tab?

Please Login or Register  to view this content.

The first part seems to work ok the second part (top) must need to be in a different place, but I can't figure out where?

Please Login or Register  to view this content.

Also here is the latest version of program

ES Program Redesign_v2.xlsm

You can add a new property from Calendar table see what you think? Also maybe you can check Owners Table and see why LastUpdate is not picking up info from ChangeLog


Thanks

u3rick

Setfocus to second page:

Please Login or Register  to view this content.

Setfocus to first page and FName textbox:

Please Login or Register  to view this content.

Tsjallie

Could not find any ComboBox1_Change() or code in download ES ProgramTest Redesign_v1d.xlsm. Sorry if I missed it but looked for a while... nothing found. You may not see this till after your Vaca!

Thanks

u3rick

Select the combobox in the form (upper left) and open the code view window.
Or do a search (select whole project option) on "combobox1_change()".
It's really there

Ok, back in town.
How are you getting on?

Tsjallie

I have made some headway and would have sent it to you but my Mac went down last night so until I can get it fixed I have no way to send it. I need you to check the code out. The forms I have done work but will need some adjusting. Hope I can get it here for you soon (Maybe By Monday)

Thanks for checking





u3rick

Think there is a problem with the file.
Get an error about an invalid object library when opening or when running a form.
Have been looking for solutions, but nothing works so far.

Don't get the error on the earlier version you posted.
Do you need the mscomctl.ocx for something?

This one's OK
But I'm opening it on an other computer. So the problem could well be on my side. We'll see.
Will look into it tonight.
Are there any specific things you want me to look into?

The workbook is OK. Problem was at my end.
On the computer giving the error I used a more recent version of mscomct2.ocx (needed for the datepicker).
After installing and registering the version your workbook uses the error was gone.
Took me more time than I anticipated (probably it was just too obvious)

This might well be the same problem as you're experiencing now with the datapicker.
You can check the version of your mscomct2.ocx like this:
- Find the ocx in c:\windows\syswow64
- in the right-click menu select properties
- in the properties window select the details tab.
- check if the productversion is 6.00.8804
If it does and the problem persists then try following
- register the ocx like this
- in the Startmenu - All programs - Accessories
- Right-click Command prompt
- and select run-as administrator (important!!)
- at the command prompt type: regsvr32 c:\windows\syswow64\mscomct2.ocx
If the problem is still not gone then try the following
- make (or renew) the reference to the ocx-file in the VB-editor
- Open the VB-editor
- Select Extra - References
- Select Browse and navigate to c:\windows\syswow64 directory
- select filetype Activex
- find mscomct2.ocx and click Open
- click OK in the references window

If you don't have the right version of the ocx-file you can dowload that version here: http://www.ocxdump.com/ocxfiles/M/MSCOMCT2.OCX
Copy it to c:\windows\syswow64 and perform the steps above to register it and make the reference.

So now I can finally take a look into the workbook

I don't think we will have anymore problems with the ocx-file. And if so, then that will probably be solved easily.

Started looking at the issues you posted.

GuestsStays sheet does not copy all formulas when adding a new line, I could not figure out why.

Excel sometimes loses track of table definitions. These are stored "deep down" in the workbook. If you encounter these kind of things just copy the entire table and paste it back. That resets the table definition and solves these kind of problems.

To be continued ...

Continued ...

There is a new sheet called ListGuestStaysPC that fills a combobox in the Stays form that I want to filtered and just show the vacant units that won't work (I think the sheet works but the combobox shows all units no matter).

I think I don't have the most recent version of the workbook. The StaysEntry form does not have this combobox yet. So I cannot comment on this form for now.
But from this version I can tell it won't work like you intended: the table ListGuestStaysPC looks at the Calendar (columns C and D) and takes that as-is including the sort order. That means when filtering on "Vacant" it delivers a non-continuous range of cells and so the combox will be filled with only the first continuous portion of the filtered cells. I guess we'll need to figure out an other way to fill the combobox with just the vacant properties.
Also I noticed that the Guests column (D) in the Calendar has a formula with an unknown (for me that is) function (_xlfn.IFNA) thus giving #Name as a result. Assume you meant to use the IFERROR-function.

To be continued ...

Continued ...

Look at Guests & Stays Activity the code all seems to work and I used mostly parts of code you made but check and see what you think.

Looks nice and seems to work properly.
One suggestion though: I think payments relate to stays, but this relation is not shown. May be you should add the StaysId to the ListPymtHis table and show that in the payments combobox. When you would ever want to report on payments made for a certain property you will need to link the payments to that property. Either thru the StaysId (recommended) or (more directly) thru the PropertyId.
Also, when you show all the stays and all the payments there is no need to make the list items selectable. In that case I would use a listbox instead of a combobox. Not a big deal, but it may be confusing for users.

To be continued ...

Continued ...

I think I made all the lists like you suggested but you can see and let me know if they are ok.

Perfect! However you need to expand these list by one row when a new owner or new guest is added. Best is to put that code when adding a record to the Owner or Guests sheet.

To be continued ...

Continued ...

Other changes also in Calendar added formulas to find units and status (Check Months column)

See my comment in post #68.
Also it's not quite clear to me what you mean to achieve with this Calendar. Can you tell me more about it's intended functionality?

Where are you using this datepicker?

Tsjallie

The formula showing the guest last name on the calendar sheet, gives the last name of the guest, if vacant it returns an #NA. If I use the IFNA it lets me turn the #NA to "Vacant"...That was the only way I could make it work!

On page 3 of the Stays form, that's where the cbo box is that should look for vacant units, not sure why your version doesn't have it?

My new computer is not working very well tonight and My Guest Activity Form won't run (keep getting a error message at the filter) and I have not change anything?? frustrating!! Anyway the upper listbox has the StayId and Stay info and data. The listbox under it has the transId and all the payment data that relates to the upper listbox. all this info relates to the Unit that is picked in the cbobox. Not understanding what you mean in reply 69?

I think I have code adding lines in each of the related worksheets, if not let me know the ones I am missing it in.

Sorry with my Excel not running well its hard to get all the info to you I would have liked. I will keep working on it and update as I can. Hope by the weekend things will improve.

Thanks

u3rick

If I use the IFNA it lets me turn the #NA to "Vacant"...That was the only way I could make it work!

IFERROR is not working with you?

Not understanding what you mean in reply 69?

Well, I assume that the payments Guests are making will at some point in time need to be transferred to the owners of property a guest stayed in.
If you don't have the payments and stays or properties linked, how will you be able to determine what you must transfer to the owner of that property?

I will keep working on it and update as I can. Hope by the weekend things will improve.

Don't hurry. I'll have another beer
And gives me the chance to play around with the calendar.

Can you send me a screen shot of where I need to add the Id to link payments and owners.

You might add the StaysId to the ListPymtHis table. From there with the StaysId you can link to the ListStayInfo table wich has the Property Code in it.
And consequently with Property Code you can find the owner in the PropertyOwner table. Remember the datamodel I posted earlier?

Looking into this it struck me that there are already a GuestStay and a GuestTrans table which seem to be twins to the ListStayInfo and ListPymtHis table.

And playing with the datepicker took me down to memory lane. I remembered strugling which this control and the ocx-files. Finaly decided to make my own datepicker in order to get rid of this ocx-mess Microsoft is presenting us with. Forums are full of misery with these files and mostly it seems that windows updates are the culprits messing up things.

It's me who should say sorry. Was looking with my nose and didn't notice you're using formulas for the lists.
Still think you're doing fine

Wasted the day downloading Windows updates!

Welcome to programmers paradise

I will upload a demo later. Need to do some translations. Guess your Dutch will be a bit rusty
I am also playing with you calendar and incorporating it in there too.

Here's the DatePicker demo. Should be in English for you (though a lot of code is still Dutch).

Got the Download and having some problems

It's plain Excel/VBA so should run ok. Maybe some (national) settings, but we'll sort that out. Only ran it on my Dutch Excel version.

Have a beer or two!

Why the rationing

What's the value of KnopFocus when the error occurs?
Does the Calendar work if you turn the line the error occurs on into a comment?
Can you run this macro and post the results from the immediate window?

Please Login or Register  to view this content.

OK. So it's a bit more complicated than just a (national) setting.
The stackspace error pointed me to a (pretty fundamental) construction error causing procedures te be called recursively.
As each call is put on the stack and the stack space is limited by memory, this stack can easily exceed the max amount of memory.

How much memory and cpu's does your pc have onboard?

For now I would put this datapicker aside as I need to do some serious rework on it and that shouldn't delay you.

I believe you have a version of your workbook containing the ocx date picker. Can you upload that version.

Hope I can get that to work with me, otherwise we would have the situation of my datepicker not working with you and your datepicker not working with me.
That would hamper me helping you with your workbook. But than again we could well workaround that as we can well do with a less complicated solution.

One other try though.
This version should be more economic on your stack space.
Don't think it will solve the other issues yet, but I hope it will not exceed the stack space and take me a step further.

Thx.

Will test your date picker tomorrow!

Will post another version tonight. Didn't do it right. Guess it's some of those days.

To bad. This is what I get when I open the frmStaysEntry
StayEntryShot.JPG
Curious. Would expect that may be the datepicker would be left out. Not the other object

Replaced the CalendarDemo in post #90.
Added a messagebox which comes up when the togglebuttons in the calendar are recognized.
If that messagebox pops up then it's clear where the problem lies.

Not easy to solve a problem when it cannot be reproduced. But it's for good mental training.
But your screenshots are very helpfull. From these it looks like the day and month are swapped, which - obviously - causes trouble.
Trying to simulate this I get the same error as you when first tried the datepicker. So that's encouraging.
So, after all, it may indeed be just a difference between your and mine Office-implementation.
And a lot of work. So no beers the next few days

Ok, I have some good news.
The MS datepicker (the one you're using) works now on my business machine. Don't now why that is, but I'm happy with that.
On that machine I can now see and run the StaysEntry form.
On my own machine - where I do the forum things - however it still doesn't work.

I suggest you continue with your datapicker and do away with mine. So we can continue with your workbook and if necessarry I can check things on my business machine.

Where were we?

Oh, I remember. I was playing with the your calendarsheet letting users select a period and than have it pop up the staysentry form.

Here's an idea for the calendar.
Take a look at the sheet Tsjallie_Calendar.
Uses a dummy StaysEntry form, but you can pretty easily replace it with your StaysEntry form.

I case no one every tells you this... you are good!

I know

by adding some of the things you listed it will be perfect

Do you want me to add these or do you want to do it yourself?

No problem. I like to do it.
Are there things you specially like to add to the calendar?

Wow, that's going bloody fast.

Forgot to ask:
what's the policy on moving in and out properties. The calendar as it is now works with monthly periods, but I believe I've seen portions of months being calculated in a sheet.
To show properties being rented/available a portions of a month I need to figure out something for that. Is there a minimum rental period? Fixed move-in and move-out days?

Hi Rick,
how are the races going?

Here's the calendar. Comments are on the Calander sheet.
StayEntry form needs some work yet. Don't wonna cut the grass from under your feet

BTW, hope we don't run into the same problem as with that datepicker.
I'm pretty convinced that that's got something to do with how dates are formatted in your (US) implementation and my (Dutch) implementation of Office.
Tried to avoid that problem by using date serialnumbers as much as possible.

Going through the workbook I see you're using two identifiers for the property: PropertyId and PropertyCode.
If they both uniquely identify a property than one could be dropped.
For the Calendar I've been using the PropertyCode, but if these codes are not guaranteed unique, I need to switch to the PropertyId.

Good luck next week with the races!

Tsjallie


Really like your calendar features, I have two question. How do I make a move in date in the middle of August if August has started? Second, is there anyway to make a reservation yet or is that still need to be completed in the StaysEntry form as you noted. Wanted to see the double click work to bring up GuestData form.

Can that be moved to my calendar so the color formatting works? or is it easier to format yours. Also wondered about making the renter name show each month on the calendar and do away with the address then it would match the vacation rental calendar.

We had some boat damage last weekend will be flying to the boat shop in Indiana to make repairs, but hope to keep some movement on this project!

Thanks

u3rick

No, I haven't given up you Just been very busy in the office lately.
The StaysEntry form I use is just a dummy, because I can't use yours as it contains the MS datepicker. We're a bit handicapped at that point.
And I love using tabs myself. They're very powerfull.
Will look into the other things tomorrow or maybe monday.