Clickable macros & variables

[Cheshire]

I'm so close to finishing my project!
Well, at least to a point where I can present it to my boss without looking too foolish.

To clean it up a lot and make future maintenance infinitly easier, I was hoping there was some way when assigning a macro to a .jpg / image in the workbook to also specify a value.

I use the same macro for over 300 objects. When the asociated picture is clicked, it searches the entire workbook for the object's serial number.

This is the code I'm using in the macro:

Please Login or Register  to view this content.

The '84DD12A' part I would like to be able to add on a per image basis. So when the next person comes to update the inventory sheets, they know to just create a picture, add the macro, specify the serial, and their work is done. (as opposed to actually having to look at my macro sheets and creating a new Serial_Number##)

I'm terribly sorry if this isn't perfectly explained, but I would really appreciate any help I can get on this matter.

Thank you so much,


-Aaron

[Palmetto]

Might try something along these lines (untested)

Please Login or Register  to view this content.

Where the user is prompted to enter the picture serial number and strPic_Id replaces the last argument in your formula (84DD12A).

Note: you don't to select cell to act on them. As you in the revised code you simply reference the range then perform whatever action you need.

BTW - if you don't actually have 65,535 rows of data to look through, why make Excel look in every row? You are also forcing Excel to perform the look up twice which is very inefficient.

It is much more efficient to do the look up once and let the look up return #N/A and use a formula like IF (ISNA(A1),"System name not found", A1)

[Cheshire]

Thank you a lot Palmetto for the tips & pointers.

The main point is the user usually has no idea what the serial number is.
I just layout 'images' that reflect the actual inventory shelfs.
People that dont know the name or serial number of their hardware, just need to point & click on the one that looks right. Basically letting them search based on visual queues and nothing more.

So serial assignment would still have to be statically assigned to each individual picture, and I'm just trying to ensure however I implement that will be equally as simple for the person that next has to manage these pieces of code as they themselfs need to add new hardware to the inventory list.

---

Thanks for the tip on not requiring to select the box. That was leftover code from way back when all I could manage was a slight reworking of the record macro program.

---

So you recommend that I shove 'VLOOKUP(R3C3,R9C4:R65535C19,16,FALSE)' into some sort of variable, and call on it? As your example is still referencing 'A1' twice, I dont know quite understand how that is avoiding redundancy as a single line cell formula?

Thank you so much,
And I hope there are a few more people with a word of two to add. =]


-Aaron

[Palmetto]

As your example is still referencing 'A1' twice, I dont know quite understand how that is avoiding redundancy as a single line cell formula?

I did not attempt to amend your formula - that is up to you to decide.

Your formula does force Excel to perform the lookup twice if you understand how the IF function works. Each argument in the function has to be evaluated whether in VBA or a worksheet function

So . . .
Argument1 says "do the look up and if is it is NA" then (argument2) give me a blank, else (arugument3) if it is not NA, then do the look up again and return the result.

It is generally poor practice to reference entire columns when performing a look up - unless you really do have that many rows of data to comb through.

[Cheshire]

Thank you again for the ellaboration Palmetto!

My situation is I dont know how much data will be added to the workbook, and I would like to try to prevent errors from popping up.

If there were a way I could have it reference the lowest occupied cell in the respective column, that would be great. But I dont know how to do that, and am focused I guess on other parts of the code regarding cleanliness. At the moment it is only ~150 lines per page, 6 pages to search through. So the system doesn't show any visible lag despite the inefficiency.

If I could just write something like:
Private Sub newCode (serialNumber As String)

And when assigning the macro to the picture to include a value for the 'serialNumber' variable, if would save so much time and space (maintenance wise).

(repeating the same 8 lines of code for 300+ objects gets big fast)

... I guess basically I'm hoping for some way to assign a function to an image.