VBA CODE for Excel 2007 & 2010

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: VBA CODE for Excel 2007 & 2010


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


    Ok, give this a shot. :)


    I added a couple more things...one you'll probably notice, the other maybe not...


    -There is now a checkbox to choose if you want to unload the toolbox every time you do something with it.
    -You can click around on your workbook with the toolbox in front of it now. (before you had to close the toolbox to navigate through the workbook)


    Check it out. Let me know how the testing goes. Good luck :)

    Re: VBA CODE for Excel 2007 & 2010


    Okay... the sheet seems to be working fine. The only glitch I notice is that the rent charged column adds one to the value from previous row when adding a new property. Can we make a "Rent Charged" w/ textbox in the add new property userform that will send any number I type in to the corresponding column?


    Also I think it would be better for the add new property userform if when user clicks add property button that the values in the text boxes reset to being blank ready for the next property to be input.


    Is it possible to add a search function to the userform that will allow me to type an address while it auto guesses at what I'm typing and upon clicking search it highlights and takes you to that row in sheet 1? Kind of like a google search bar lol.


    Everything else is wonderful... Great job and it is greatly appreciated!


    Please use this sheet as I added properties and changed some of my formulas.... -------> forum.ozgrid.com/index.php?attachment/44946/

    Re: VBA CODE for Excel 2007 & 2010


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


    Try that. I didn't test the remove buttons, but they were working before...should still work. The comboboxes will already act like you want. If you start typing something that's on the list, it will predict and drill down what you're typing.


    -Column Q is now a hard number for Rent.
    -"Find" buttons added to go to the property you're looking for on either sheet. Seperate buttons...I know you only wanted sheet1, but that's ok. If you never use it, it doesn't hurt anything. :)

    Re: VBA CODE for Excel 2007 & 2010


    LOL thanks! I'll give it a whirl here a little while... Thanks for all your help... you have been awesome!

    Re: VBA CODE for Excel 2007 & 2010


    I created userform2 to be my splash screen. I know how to edit this now, so it will be a work inprogress.


    Possible Additions:


    1) Clicking “add property” on tab 1 of toolbox will add the property and then reset the text boxes to be blank and ready for next property input.
    2) Clicking search for good investments will populate sheet 2 and then automatically close the toolbox and go to first entry’s address on sheet two.
    3) Clicking search for good investments will

    a) only add entries on sheet one that are not on sheet2
    b) leave any previous entries in place but they must still have “TRUE” in column AS. (Is this even possible? If not I can just clear the sheet before pressing search for good investments. No worries…)
    Another option might be to let it run and duplicate entries, but then have it remove duplicate entries at end of macro. I’ll let your brain do the thinking since you do so well with it.

    4) "Cancel" button on tab 2 of toolbox at bottom of userform1
    5) The headers are not on sheet two matching sheet one. I didn't want to edit to break codes, but it would also be nice if the header above the dates added on far right of sheet had a header "Date Listed"


    Other than this fine tuning the sheet is working great!

    Re: VBA CODE for Excel 2007 & 2010


    :party:


    We aim to please. If you have any other issues, let me know. Like I said, I keep everything for quite a while, so I can either resend or troubleshoot for/with you.

    Re: VBA CODE for Excel 2007 & 2010


    Please... is an understatement. I thank you for your continued support with any issues that might arise. I am going to save several copies. I was reading about VBA all day and now a lot of this starts to mkae sense. If I read right... then you can specify objects by worksheet(1) to prevent naming convention issues. Is that right? Obviously I am not an overnight VBA guru, but I will continue to learn. Now that I know how to create userforms I just need to learn how to make it execute macros. I see examples from creating the splash screen, but it will take more time to dedicate brain memory space to it lol... Thanks for the help. I will definitely be on here if anything else pops up ie. new projects or issues. Thanks!

    Re: VBA CODE for Excel 2007 & 2010


    You can use the sheet index:


    In your workbook, as it's set up right now...with Sheet 1 on the left and sheet 2 on the right you could use:


    Sheets(1) for sheet1, and Sheets(2) for sheet2. However, if you move the sheets around or add sheets, that may change. Sheet index is always from left to right. Sheets(1) right now, is sheet1....if you move sheet2 to the left of sheet1, then Sheets(1) would refer to sheet2. So you have to be careful, but yes it does remove sheet name problems. Index doesn't care what the name is.


    OR :)....there's always an or....


    You can use the sheet codename:


    As it is right now:


    Code
    Sheets("Sheet1").Range("A1")
    Code
    Sheets(1).Range("A1")

    and

    Code
    Sheet1.Range("A1")


    all mean the same thing.


    The sheet code name is found in the VBE project explorer and it is NOT the one that's in parenthesis. For instance right now under "Microsoft Excel Objects" you have:
    Sheet1(Sheet1)


    if you change the name to "Properties" it will show:
    Sheet1(Properties)


    The Sheet1 is the code name and it will not change unless you delete the sheet then add it back in, then it may have a different code name... and, in hindsight, that's what I probably should have used in all references...to use that, you don't have to wrap it with Sheets( ), it's simply:

    Code
    Sheet1.Range("A1")

    as shown above.

    Re: VBA CODE for Excel 2007 & 2010


    Wow... you obviously know this stuff... I think I will start by being specific. then move to make the code more efficient. What is the tools button called? it isn't a ribbon button... trying to look it up... maybe I will just look at the codes lol. Thanks for everything. It has been my pleasure.

    Re: VBA CODE for Excel 2007 & 2010


    I'll look at the code... but I was referring to the button that opens the userform1 or is it a userform? I want to learn how to put the button on the excel sheet that will open a userform I create. If you are willing I might have another project that vba could help a lot. It is more of a budget thing...

    Re: VBA CODE for Excel 2007 & 2010


    I'm always willing. Things like this are a learning experience for me too. Try to think it out and make as many requests as possible from the beginning, which you did pretty much :) .....had a few additions, but that's inevitable. But especially on the workbook side, the closer we get to what you want before the coding begins, the better off we are. As I'm sure you've found out by now, coding isn't intuative like formulas are. If you code something to go into, say column G, and then insert 2 columns between C and D, it will still put it into column G...it don't know any different.


    Don't get me wrong, I don't mind changing things, but it can be stressful during testing if you forget to change one reference.


    At any rate, yes I'm willing to give anything a look...it'll will be a process, just like this was, so give it a week-ish lead time to make sure we get it done before you have to go live with it....if you're on a deadline.



    .
    The controls toolbox for worksheet controls is in the Developer Tab, "Controls" group, "Insert", and the one you want for something like that is a "Forms" button, rather than an "ActiveX" button. Create the button, then right click and assign macro...and the code is something like:


    Code
    Sub OpenTools
     Userform1.Show
End Sub

    Re: VBA CODE for Excel 2007 & 2010


    If you do have something new, let's start a new thread. 1) this one is getting long, lol. 2) Seperate projects will interest different people, and mixing something different in here will get confusing. I'll keep an eye out though. :)

    Re: VBA CODE for Excel 2007 & 2010


    Ok on the same project I need to make some differences if at all possible. Here are the suggested changes: This file is the one that needs it -----> forum.ozgrid.com/index.php?attachment/46013/


    I would like to delete columns S, W, AB, AD, and AF from both sheets. I tried but it broke the code.


    Column F can be changed to "Property Type"


    I would like to add a control on UserForm1. Preferably buttons that will have text next to them "SFH", "Duplex", "Triplex", and "Quad". When info is put into the other parts of Userfrom1 I want to click what type of property it is and then click "ADD PROPERTY" and have the term listed next to new controls on userform1 pasted into column F on the new entry's row.


    On the top of sheet one and two I have four colored cells that represent the color formatting I would like each new property to take on when a new entry is added. For example, if I add a new property and click Quad The new entry's row will be "filled" with light red.


    Remove restrictions in Userform1 that require an entry in the user input blocks.


    Could you please help with this?

Participate now!

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

Register Yourself Login