need macro help (excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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 tutorial. If you’re not familiar with the use of Personal.xls, I strongly recommend you to read it before creating your macro.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote