VBA CODE for Excel 2007 & 2010

    Re: VBA CODE for Excel 2007 & 2010


    You are insane with this lol..in a good way... Glad you took the liberties that you did. I was deleting sheet 2 entries by selecting all and deleting. Thanks for those adds. I don't know how many numbers the MLS have I think seven sounds right. I may have seen some that are eight but not sure. When I talk to my real estate agent I mention address and he says "What's the MLS #" lol... That is the only reason I am going to record it. Thanks for the date stuff... You can allow the MLS to input up to 9... I found one that was 6 but most are 7 from a quick glance. I do not know if it varies... ok my quick research says that MLS codes go by the date built and area. They are chronological allegedly. Can we just remove the parameter for MLS. I will only use five digit zips with no intentions to go to full zip codes. Thanks for all the other parameters you set... smart idea... sounds great...


    How do you get sheet 2 to ....updated5.xlsm? I did a copy paste but then the macro didn't copy entries from sheet one to two... sorry I know I am illiterate with excel lol...is it an import kind of task?


    Also the freeze pane went away and I did not see the toolbox in sheet two. I tried to redo the freeze pane but it either did a large pane or just the top row.... Any advice is appreciated... Thanks!


    I am trying to figure out what is going on with those three entries you referenced... if you want you can just delete them as I can add them with new code to see if that fixes it. I also noticed my conditional formatting went to hell when I added the MLS # column lol... but I can fix that later on.

    Re: VBA CODE for Excel 2007 & 2010


    Re-download the attachment in this post and the one called ".....sheet2" in my previous post. Open both and leave them open. Go to the "....sheet2" workbook and right click on the sheet tab and select move or copy...when the box pops up, find the dropdown list at the top that says "To Book" above it. Drop that list down and select the workbook called ".....UPDATED AGAIN5" and click OK. This should close the "...sheet2" workbook and the ".....UPDATED AGAIN5" workbook should have both sheets in it. The button will stay, the frozen panes will stay and the code will remain intact.


    -MLS now allowed up to 9 digits.



    forum.ozgrid.com/index.php?attachment/44916/

    Re: VBA CODE for Excel 2007 & 2010


    Not a ribbon tab. The sheet tab at the bottom...where you click to change from one sheet to another. Where the sheet name is.

    Re: VBA CODE for Excel 2007 & 2010


    So it moved successfully, but no toolbox on sheet 2...


    Freeze pane stayed as desired...


    "TRUE" entries didn't copy over..

    Re: VBA CODE for Excel 2007 & 2010


    I deleted entries from the data on sheet1 this morning. I clicked to highlight row and then right click delete. Did I break the code that already existed? Maybe we should put a delete entry from sheet one button lol.


    Should I try replacing all the macros in updatedagain4 with the ones in latest edition?

    Re: VBA CODE for Excel 2007 & 2010


    It didnt put them on sheet two... but I do see the button now... is it just my computer that it isnt working on?


    Some columns seem to be adding one instead of what they are...

    Re: VBA CODE for Excel 2007 & 2010


    Let me check it out today and see what's going on with it, lol. I'll post it again later today.

    Re: VBA CODE for Excel 2007 & 2010


    Ok thanks .... I was just reading a book on VBA and learning about how you made the userform with different "controls" etc. I am trying to make a splash screen now lol... Dont know how it is going to look but we will see lol.

    Re: VBA CODE for Excel 2007 & 2010


    I'll post up a little rant about coding too.


    Few good habits to get into.


    -Always declare all of your variables. You can force this by typing "Option Explicit" at the top of each module. If you Open the VBE, click the Tools menu, and click Options then check the box that says "Require Variable Declaration" it will do it automatically on any new modules you insert.
    -Always rename any userform controls you'll be calling regularly in your code. "tbAddy" is easier to identify than "TextBox1".
    -Get in the habit early on of indenting your code...it makes it MUCH easier to read and a missing "End If" or "End With" or similar will show itself pretty quickly. When you get started, you'll see what I mean.
    -Use the recorder, but don't just blindly use code that was made by the recorder. If you get stuck on some syntax, record yourself doing what you're trying to code to get it, but just use the applicable line(s) out of the recorded code.
    -If you see code like:

    Code
    Sheets("Sheet1").Activate
Range("A1").Select
Selection.Copy
Sheets("Sheet2").Activate
Range("B4").Select
ActiveSheet.Paste


    all that can be combined into this:


    Code
    Sheets("Sheet1").Range("A1").Copy Destination:=Sheets("Sheet2").Range("B4")


    -It's rarely necessary to Select a range to work on it.
    -It's rarely necessary to Activate a sheet to work on it.
    -If screen "flicker" is slowing you down, you can stop it with this line:

    Code
    Application.ScreenUpdating = False


    and make it true at the end.



    I guess that'll due for now, lol...can't give ya all the tricks. You'll figure it out as you go along. Good luck to you!!

    Re: VBA CODE for Excel 2007 & 2010


    Okay so it is working now. I have updated the conditional formatting and fixed the formulas in the TRUE column


    Does the button Clear sheet 1 completely dump the sheet or does it grant the choice of which property? I would like it to not delete the whole sheet, but how user can select a property to add to sheet two, except for removing an entry by its address from sheet 1. Hope that makes sense. Wait on it though... I am going to fix the values in list price and such first. When I fix that I will send you the sheet again for changing the delete property by address from sheet1 feature. Thanks for fixing it...

    Re: VBA CODE for Excel 2007 & 2010


    I need to update the market values, but it should not matter for the last of the improvements being worked on.
    See the last post for the other things I would like your help with....


    After this is done I have some properties to add so guess it will be the first official test run....


    Here is the attachment...


    ----> forum.ozgrid.com/index.php?attachment/44936/

    Re: VBA CODE for Excel 2007 & 2010


    Not a problem. Will wait for the next update. So you want a dropdown, similar to the one to delete items off of sheet2, but to delete single items off of sheet 1? Right now the clear button clears the whole sheet. Do you want that removed or left as an option?


    EDIT: got the update, but still need to know how you want to handle things per questions above. :)

    Re: VBA CODE for Excel 2007 & 2010


    Uhhh.... let's remove the clear all for sheet one button and replace it with a drop down list that wil allow me to delete one entry by choice from sheet one. Yes similar to the one for adding to sheet two except to delete from sheet one... Thanks! I am extremely excited to see the final product in action now...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login