Posts by BazN

How do you prevent a user from viewing the macro code without locking out access and changes to the excel spreadsheet that contains the macro

A couple of years ago I built a spreadsheet that contained a few little macros, and worked fine. Haven't used it since. Now I have to revisit it and look at the code. BUT it is password protected and I have forgotten what password I used. Is there any sneaky way to get round this so that I can view the code and edit if I need to ?

I have an excel workbook with two macros that work fine (thanks largely to this site...wish I had found OzGrid weeks earlier !), but now I have another query.

I have two macros, each with its "start" on-screen button. I find that with my sometimes unsteady fingers I will unintentionally click twice, resulting in two rows of data being instead of the required one. I have seen several posts on this and other sites describing the setting up of double click, but they all seem to cover doing some specific task like switching colours or shifting data and so on. I don't want the button to do anything at all except to Run the macro when double clicked.

Any simple solutions for my simple brain ?

Hi again Carim

Have been away from the computer for some time, but want to thank you for your help.

Using the suggestions you gave , plus some experimenting with some ideas I had, my job now works fine.

Having said that, I am sure an expert would find a more compact coding, but I'm happy with the fact that it does what I wanted and I actually have got my excel sheet doing some added fancy stuff that I never planned on doing.

Some progress !!

Further to my post of this morning, More of a fluke than anything else, but I found that when I clicked on the down arrow in the General header I got the Worksheet option complete with code. This applies to both the examples you sent.

Furthermore I can get it to work in my practice worksheet, one value at a time, but only if I retain your first line. If I change that to some other sub name etc I get error messages and/or no action.

Is there a simple fix for that, and what should I call a Macro, should it be the same as the sub name?

In order to transfer five values would I need to have extra subs within the sub? I tried just repeating the sub wording after the first lines (before end sub) but that didn't seem to want to work.

Thanks in advance for any help you can give.

Yes, both Test BazN and Test BazN V2 "work", as downloaded again this morning.

What does happen though is that when I try to look at the codes (using Alt F11 for example) all I see in the code area is "option explicit" and that's it.

I did write down the code for Test BazN , as I saw it in the Project Explorer the first time I downloaded it.

While I saw the code for Test BazN V2 the first time, I never made a note of it.

As I said earlier, I have been unable to get the code used in Test BazN to work in a spreadsheet of my own.

Testing your patience I know, but I feel that we are close to solving my original problem so would like to continue.

Incidentally I most likely don't know how or why the code does its thing, and I don't need to know as long as it works.

Firstly, In my earlier post I was referring to my doubt whether I would manage to attach a file to my reply, not about setting up your test.

In answer to the questions

1/ Yes, on the first couple of times I looked at your Test BazN and Test BazN 2 they worked. I never got the code to work in a small trial worksheet of my own, and now they don't seem to work as originally sent.

I am sure that I have wrecked something while trying to copy the code to my use.

The Project viewer now seems to be full of mixed up bits of various attempts.

2/ To fill you in, my "real" job is connected with separate but linked workbooks that crunch a large number of engineering measurements and come up with three individual values for any given job. A job may be repeated several times, with new values to be recorded in next available cells in the row for that job. A new job repeats the process in next available row and so on. There are no formulas in the cells used here, they are all copied from the linked worksheet as values.

The attached sheet is a scaled down simulation of the real thing. If we start with apple, the 3 values at upper left are to be copied at first to cells C12, G12, K12. Next time that job is done there will be new values, to be copied to D12, H12, L12.

If a job "pears" is done, that would start in this example at row 13 and the same process starts again.

It must be noted that the jobs could be placed in any row within the sheet, not just always next down from the previous one.

Questions:

Should I disable update external links and save external link values in the worksheet options?

Is the "Worksheet_Changes" in your code a sub or macro name or a necessary piece of code?

Is the "ByVal...." bit just shorthand for something like Dim Range as Value?

Hope this helps you. I really thought I was on a winner with your first reply, but am obviously missing something.

My first chance to get back to this project. Many thanks for your help so far. However I now have all sorts of problems.

When I revisit your two suggestions, to look at the code, I usually see only the line "option explicit" and nothing else.

If I do somehow manage to see the code I get either a blank Macro dialog box or the message "ambiguous name worksheet_change".

What am I doing wrong? At one stage last week when it all worked (for your first suggestion) I could not get it to work in my little simulated sheet.

What if anything do I need to put in the macro before or after your sub.

I have other questions, but for now would be happy to sort out these problems.

Your patience is much appreciated.

Amazed at promptness of reply. Provided I get this reply setup properly you will see a mock-up simulating the relevant bits of my actual workbook.

The input numbers at the left (B4 etc) change regularly, so I need the next empty cell to be populated as that happens.Book1.xlsm

Very much a learner here.

In an Excel spreadsheet I can calculate the location of the next empty cell, which could be anywhere within 300 rows and 10 columns, depending on various situations.

That location is identified in say K4 as being for example G12. I need to copy and paste a number from say B4 to the cell G12.

No matter what I try I finish up going to K4 every time (or getting error messages ! ). How do I tell the operation to go to the nominated cell instead the one that nominated it?