Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    need macro help (excel 2000)

    Good Evening to all ,
    I am in need of a macro that will be able to be used in any work sheet. The macro needs to start in the current active cell and ask for a number to be keyed. It then must proceed to the right (next cell) and ask the same question again. This must be able to go on for 6 to 10 times ( I need to be able to adjust the number on different sheets). It then needs to drop down one row and go all the way left to the first cell that does not contain any information. (ie) If cell B1 and B2 have text in them, then the macro needs to stop at cell B3.---- I hope this makes sense.... Thanks in advance for all the help

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: need macro help (excel 2000)

    Why not just have the user enter the information in cell directly? You can use validation to limit what can be entered, and protect/unprotect or change the background color of cells in which you need values.

    You can also use the worksheet deactivate event to make sure that all required cells have values before the user is able to leave the sheet.

    As far as the macro goes, you might start by recording the basic steps you wish to perform and then adding some intermittent code using command buttons on a user form or even use an inputbox. If you can post a small (less than 100k) example of what you are trying to do I am sure that someone here can help you fine tune the code.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: need macro help (excel 2000)

    Thanks Mike,
    I will give the input box a try. I know I can set this up and save it to just one spreadsheet, however is it possible to set up this type of macro as a stand alone to be used on command?
    tia

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: need macro help (excel 2000)

    If you want a macro to be available in all your spreadsheets, you can place it in Personal.xls.

    Legare Coleman has put together an excellent <!post=tutorial,118382>tutorial<!/post>. If you're not familiar with the use of Personal.xls, I strongly recommend you to read it before creating your macro.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: need macro help (excel 2000)

    I would advise against building this into personal.xls *unless* you would be the only user of the application. I one ever wants to distribute the application to other users, it is better to use a normal workbook, which can either be in the xlstart folder (the wkbk should then be hidden from view) or saved as an add-in and installed as such.

    Rememeber, Personal.xls is "personal", it contains macro's people recorded or wrote to ease theri daily tasks, so you would need to merge your personal.xls into theirs, whcih is more cumbersome than simply adding one workbook to their system.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: need macro help (excel 2000)

    Thanks Hans, I have printed the tutorial and will read it in just a few minutes.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: need macro help (excel 2000)

    Pieterse,
    I understand what you mean about "Peronal" --- I will keep a copy of the final macro in "MY Personal" file and build the individual worksheet / book for others to use and include a copy of what is relevant to that particular book. I hope this makes sense to you. Thanks for the advice.
    Bill

  8. #8
    New Lounger
    Join Date
    Dec 2001
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: need macro help (excel 2000)

    On the same line of thought. Can you help on storing macros. We are on Office97, and when storing Macros in Excel and selecting to save them in the Personal Macro Workbook they store just fine, as long as I dont close excel. If i close excel and come back in, all my macros are gone. Any suggestions or help wuuld be appreciated.

    Thanks

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: need macro help (excel 2000)

    When you save macros in your Personal.xls and then close Excel, you should be asked if you want to save the changes to Personal.xls. Respond yes.
    Or you might try saving the Personal.xls as soon as the macros are done being written:
    Go to the Visual Basic Editor (press Alt+F11 if it is not running).
    The menu at the top will let you save Personal.xls.
    If these two methods fail to save the macros in Personal.xls, I would suspect you do not have Excel installed locally on your PC, and there may be problems with rights to the Personal.xls file that is stored on the network.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •