Basic VBA Questions

[Micks87]

Hi peeps,

I am an intermediate excel user and recently (on saturday!) just started learning VBA.

I have a problem and I am hoping if anybody can help a Newb out.

I am trying to have a macro run when an excel sheet is opened which follows with a series of inputs (Length .... Input -> X, etc). I have completed this, but if the macro is run again these values have to put in again wiping the original value.

Is there a way of skipping input. The tabs which appear are OK and Cancel. If i cancel, the cell turns to zero. A skip option would be ideal. I have places the code i used below.

If anyone could shed some light on the issue I would be most greatful.

Cheers,

M

Please Login or Register  to view this content.

[alansidman]

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[alansidman]

Instead of having the macro execute on opening, why not put a command button on your spreadsheet and only update when you run the command button.

[Micks87]

Ok thanks, I have not yet got to that on my VBA self training. I must google that and see how it works.

Do you think it is a better way to run a program? Where would you find some sample code on this?

[Micks87]

Thanks I just got the command button sorted. It works well, its probably a better way than an auto macro.

Is it possible to put in a "Skip" command so data is not overrun is macro is re-run?

[abousetta]

Had the same problem when I started to learn how to use vba...

You have to use relative referencing rather than absolute referencing. It's not clear how you have your excel sheet set up, but it's usually better to have all the data from one entry in a row rather than a column. For example where would the next set of data go (e.g. G8 to G15 or H1 to H7)?

If G8 to G15:

Please Login or Register  to view this content.

[Micks87]

Great that solves a bit of the problem. I suppose you would really need to see the full sheet in order to see what I am looking to do.

I am creating a template to design a structural member, where a single user will input a few variables which in turn will present an answer.

For example, there could be 10 variables, and in designing the "beam" one variable may change leaving the others constant (eg: length etc). So when I run the macro I am prompted with "Length (mm)....'input' " with the option to press 'ok' or 'cancel'.

Is there such a way to have a 'skip' button so that the value is not deleted when pressing 'ok' or 'cancel'. Or is there a way to put an 'if' statement or some smart formula in to stop the value becoming deleted/zeroed and stay constant with the 'ok' command?

I understand this could be a very silly question, but bare with me please I have only started using VBA.

[abousetta]

Maybe something like this:

Please Login or Register  to view this content.

[Micks87]

Abousetta that is just near perfect. I have a long way to go to learn that code.

Again sorry for the questions. The code works great so nothing is zeroed.

But if when I get prompted with the "Length (mm)....'input' " and I want to input a value for this, I would ideally like to over-right the initially referenced cell so it is not put it to the end of the row/column? I would want the cell if zeroed not to change.

Your help is greatly appreciated

[abousetta]

The data from the code will go into the next available row. If you want to replace data then I need to understand what you are doing in more detail. Preferably upload a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Micks87]

Ok will do, I will tidy up a couple of things and get it up. I really appreciate your help.