VB Macro to Auto Insert Two Checkboxes For Each Image in Excel 2007 Workbook

Hello Excel Gurus,

I have tried everything I could think of and I just can not make this work. What I have is an Excel 2007 Workbook that will contain a tab named "Output_Export_Report". In that worksheet there are 4 columns and 2 rows of data for each product that is only used for helping the user choose which image is appropriate per the description. There are 48 other columns (Columns "E" Through "AZ") that can potentially contain images for each product. In column E the header will always state "Stibo" which translates to "Primary Image". The raining columns headers are automatically renamed to image1, image2 etc.. if they contain a product image. If there is no other product images for that specific product, the column header names will be unknow1, unknown2 etc... and we can ignore those columns.

What I am trying to do is create a macro that will insert two CheckBoxes (or OptionButtons) in the row immediately above each image (in the row that has the image local drive URL) for all the images so that a user can select either "Primary Image" OR "Alternate Image". I can only have one "Primary Image" per product however I can have numerous "Alternate Images" per product. Once the user has gone through the entire worksheet selecting Primary and Alternate images for each product, I need to have the results written to another worksheet in the same Workbook (Lets call it "Image Selections") that has (mdm_name, iref, mfr_pn, description (col A-D)) and the users selected "Primary Image" then any "Alternate" images in the remaining columns. So basically the output would be [Row 1 = Existing Headers], [Row 2 = (Col A) mdm_name, (Col B) iref, (Col C) mfr_pn, (Col D) description, (Col E) user selected "PrimaryImage", (Col F - ?) user selected "AltImage1",2,3 etc]. I'm only interested in returning the Image Name and not the actual image in the output.

I have code that will place a CheckBox in each row above each image however, my code only goes as far as filling one columns with of CheckBoxes. That's all. They are not linked to any cell or return any values. That's where I get lost. I'm not sure if it will help to put it in here at this point because I am probably off base with my approach. I have attached a sample workbook with both "Output_Export_Report" and, "Image Selections" tabs example for clarification.

Hi jcook1100,

I'm only interested in returning the Image Name and not the actual image in the output.

If this were my project I'd implement your requirements as follows:
a. Your Sheet 'Output_Export_Report' design looks fine as is.
b. I would put CheckBoxes on top of the cells that contain the image names.
c. If necessary I would hide the image names (by custom formatting the cells that contain the image names with three semi-colons (;; [no parentheses - and no smiley], which means hide the contents of the cell.

See the file associated with post #2 in the following thread: http://www.excelforum.com/excel-prog...sition-it.html
The file creates active X checkboxes in cells as required. It also has a class module that will identify which CheckBox has been checked if you need real time response.

Whether you need real time response or not, you can retrieve the contents of the cell underneath the CheckBox by obtaining the 'Top' and Left' properties of the Checkbox. With that information you can calculate the address of the underlying cell using something like:

Please Login or Register  to view this content.

Lewis

Lewis,

Thank you for your reply. Please see my below code (What I have so far). Its ugly I know. I am having difficulty using the referenced link in your reply and thus, I cant really use your code until I do. So far it does iterate through all the columns and get rid of unwanted columns and formats the column widths to be what I need them to be in order for the check boxes to accurately insert. But that's where I am at an all stop. Can you take a look an see where I am going wrong?

Please Login or Register  to view this content.

Hi,

I will post the code to solve your problem in a few minutes. Since you are interested in learning, I have the following suggestions on how to improve your existing code.

1. Use of 'On Error Resume Next'. A blanket use of this statement will ignore actual errors and mask problems in the code. There are legitimate reasons for using the statement, but usually followed by your code checking for errors and processing them (or ignoring them) as required.

Please Login or Register  to view this content.

2. You already have a value in those cells (image name). The LinkedCell will overwrite the contents of the cell, and destroy the image name.

3. As written, 'c & ToRow' evalute to "52" which is an illegal Range. In the current context, the column number must be converted to a character first, so 'c & ToRow' evalutes to "E2". There is a function in the code in the next post that does that.

Please Login or Register  to view this content.

4. I couldn't get this to work properly (always returned 52, when I knew the answer was 7).
My favorite constructions for finding the number of rows/columns used is:

Please Login or Register  to view this content.

I like this construction because it seems to work for me especially after the number of rows (and/or columns) has been changed. I have used other constructions similar to yours with varying degrees of success.

----------------

Please Login or Register  to view this content.

5. exists = InStr(Cells(iColumn), "unknown") = 0. I'm not sure what you are trying to do here with the two equal signs. What VBA does is evaluate the first two expressions and if they are the same, the result is 'True' (boolean) and if they are not the same they evaluate to 'False'. Then the line evaluate to 'True' if the 'True' (or 'False') value equals 0. This can never happen in my version of Excel (2003), because Microsoft assigns a value of 1 to 'True' and -4146 to 'False'.

6. Else: Columns(iColumn).ColumnWidth = 25.71. Use of the colon to put multiple statements on one line (in my opinion) is a very poor construction (although it is perfectly legal) and should be avoided.

-----------

Please Login or Register  to view this content.

7. Use of this construction inside a for loop where i2 is the index, is poor programming practice, even though the language allows the construction. In your context you probably should have used for i2 = something to something Step 2.

This was written to help, not to criticize, and I hope it is taken in that light.

Lewis

Remainder of Option button code (must go in same Module as the other Option Button code):

Please Login or Register  to view this content.

Lewis,

Thank you very much. Let me digest your recommendations and make the changes necessary to my code. Currently this is the only project on my plate that is top priority. So my code has changed quite a bit since my original post. Most likely, your suggestions are the way to go. Give me a little bit to run through all this. Thank you again for all your help. (BTW, why are you using Excel 2003? Office 2003 has reached EOF) just curious.

Lewis,

If I am reading everything correctly here are my answers to your questions/statements just to make sure you understand what I am doing and where I am attempting to go with this. I truly do appreciate the advice and help because I am certainly not a pro at VB. I hope the below can clarify what I was trying to attempt to do.

No. 1) I was using 'On Error Resume Next' to test my code past the error thrown by my usage of .LinkedCell = (c & ToRow). Otherwise when I run it, the error would not let me go any further. I actually forgot I had that line still in there. I have commented it out and re-ran my code to ensure all errors have gone away. I do plan on adding error handling statements to each of my modules to ensure I don't lock up an employees Excel when utilizing my code.

Please Login or Register  to view this content.

No. 2 & 3) I am totally tracking with you here. I was confused by thinking the CheckBox it's self held a variable of True/False that I could use so that when evaluated to True, I could get the contents of the cell the CheckBox was in by using "LinkedCell" properties. I have since learned that the CheckBox writes True/False to the linked cell. Additionally, I realized what I was doing it wrong with the "LinkedCell" assignment and addressed it this way. However, I will certainly look at your code and test the implementation.

Please Login or Register  to view this content.

No. 4) ActiveSheet.UsedRange simply looks at all the cells in the active worksheet and determines the last row and column data resides on that sheet. If I did not remove all the column headers first, the UsedRange function would include all of the "unknown#" columns too. Basically it dynamically sets a range for me that I can hold in a variable in which I called it "rngMyRange". When you tired to use it, once the InStr function failed to remove all of the unneeded columns, the UsedRange function counted all the columns (52).

No. 5) In VB 6.0, there is a more robust string function that has been implemented. Basically I am defining "what" exists with "If Exists =" then using the InStr function to find the literal word "unknown" in the column headers. There is an automated report generator that is making these Excel files and the report generator can not insert column headers on the fly. So instead we code it to write the same headers for each report reserving each header for a specific column of data. The column headers that state "unknown1", "unknown2", "unknown3" etc.. are reserved for addition images if they exist in our DB. Since the InStr function returns the first found occurrence of the string, I had to manipulate the logic to understand what I want "true" to mean (hence the boolean). Instead of the InStr returning 1 it kept returning 0. So I changed it accordingly and it left the needed columns in place

No. 6) Using the colon after the Else....I dunno LOL. That was how the syntax was presented to me when I googled "If ElseIF".

No. 7) I wasn't sure exactly how to approach this one since the only "For" loop I know increment its index by 1. In my case, I needed it to ignore the header column and then start at row 2, format row 2 height, then jump down 2 rows and repeat until the end of used rows. The second "For" does the same thing but starts at row 3 then jumps down 2 rows again, formats the row and repeats that pattern until the end.

Lewis,

Just a couple questions for you about your code.

First before I ask you a series of questions. I knew there were more streamlined methods of coding my macros. With that being said, I am certainly going to study your alternate methods and incorporate them in any way I can since several of your macros run much faster than mine do (however I don't disable any Excel options when I am testing my code so I know that plays into the speed of processing).

1) The Poll (either CheckBox or OptionButton) module runs however, it never enters into the IF statement. It always evaluates to "False" so it just goes to the EndIf >> Next sh. I'm not sure what exactly its supposed to do other than what the title states. For some reason the "ActiveSheet.Shapes" keep pulling "Picture(some number)" as the name however the Alternative text for the Image contains the actual image name. Additionally, the row immediately above each image contains the image name at the end of the path. (I don't know if that helps any).

2) The GroupBox Sub Routine creates grouping horizontally. Since there can only be one "Primary" image and up to three Alternate images (I was just informed yesterday that there could be numerous images per product to choose from however; there can only be a "Maximum" of 4 images total per product due to the limitations of our image rotator on our website).

2-A) How hard will it be to change your code to accommodate for these stipulations?
2-B) Can GroupBoxes overlap each other? Meaning, since I can only have 1 primary image and up to 3 Alternate images maximum, is there a way to use group boxes to enforce these rules or will it have to be grammatically written to handle that logic?

Lewis,

Just wanted to show you where I was at with this prior to you posting your code. I am at the point where I wonder which direction I should go from here. Just a couple notes about my logic and the approach I was taking with some issues I have run across. (Again, I have not written enough macros to be 100% sure this is the best approach).

1. I have linked my CheckBoxes to cells far outside the "UsedRange" to the right (@Col XDA thru XDC). The even number rows retain which image is primary and the odd number rows retain the alternate image selections. Since primary images are processed first and written to the first even numbered row (row 2), Columns A, B, C, & D are the corresponding product details. Now that I have those relationships created, all I need to do is loop through each even numbered column (A-D) plus the results in XDA thru XDC and write it to a new tab as the"results".

2. I noticed that when a CheckBox is inserted with a LinkedCell property established, the LinkedCell does not contain "False" even though the CheckBox is not checked. The only time True or False is written to the cell is at the point where someone physically clicks the CheckBox OR by setting the CheckBox .Value to xlOn when I insert them, So at the end of the macro you will see that I call a small sub that changes that property back to xlOff. Now there is a corresponding cell that holds the state of the CheckBox for me to use in logic further down in my Macro Processing.

3. If there is any column or row that does not format properly in the first part of my code, the CheckBoxes will start to shift down into the rows below where it is supposed to go. I could not figure out how or why your code did the same thing no matter what the original row and column width and height is.

Below is what I had before I received your response. First to run is the TestAddRows() >>> Then (After image selections have occured) run the ValidateImgSelections(). The other Module is for Unchecking CheckBoxes and Deleting CheckBoxes if someone needs to clear the worksheet for testing (or whatever for that matter). I have not written the code for writing the results to anther tab yet.

Sincerely,

JC

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi John,

I was confused by thinking the CheckBox it's self held a variable of True/False

It is true, that when using formulas, the only way to get the value of the CheckBox is by using a 'Linked Cell'. You can get the value programmatically if you know the CheckBox name (code excerpt):

Please Login or Register  to view this content.

the only "For" loop I know increment its index by 1.

The following link includes 'For Loop' syntax and a few examples
http://msdn.microsoft.com/en-us/library/5z06z1kb.aspx

If omitted the STEP in a for loop is 1, otherwise you can set it to anything you want.


Thanks for all your other responses in post #9. There is no need for you to reply to this post.

More to follow.

Lewis

From your post #10:

1) The Poll (either CheckBox or OptionButton) module runs however, it never enters into the IF statement. It always evaluates to "False" so it just goes to the EndIf >> Next sh. I'm not sure what exactly its supposed to do other than what the title states. For some reason the "ActiveSheet.Shapes" keep pulling "Picture(some number)" as the name however the Alternative text for the Image contains the actual image name. Additionally, the row immediately above each image contains the image name at the end of the path. (I don't know if that helps any).

It works for me. Even though it's gigantic I'll try to upload my file that implements the code. The key to everything working correctly is the CheckBox names (e.g. CheckBoxImgAG0198 is the 'ALTERNATE' CheckBox in cell 'G198'. My speculation is you want to use something like the Polling module to create the 'Image Selections' Sheet. The Polling module processes the CheckBoxes in the order they were created. Since columns are the outer for loop, the processing is done by columns. If rows were the outer loop when the 'CheckBoxes' are created, then processing would be done by rows. The output for the last several lines with a few CheckBoxes checked looks like:

Please Login or Register  to view this content.

2) The GroupBox Sub Routine creates grouping horizontally. Since there can only be one "Primary" image and up to three Alternate images (I was just informed yesterday that there could be numerous images per product to choose from however; there can only be a "Maximum" of 4 images total per product due to the limitations of our image rotator on our website).

2-A) How hard will it be to change your code to accommodate for these stipulations?
2-B) Can GroupBoxes overlap each other? Meaning, since I can only have 1 primary image and up to 3 Alternate images maximum, is there a way to use group boxes to enforce these rules or will it have to be grammatically written to handle that logic?

Since 'GroupBoxes' are not used with 'CheckBoxes', and I think that 'CheckBoxes' are the best solution, "GroupBoxes" don't come into play. I will implement a new Event Handler that processes all the 'Primary' or 'Alternate' images on a Primary or Alternate row of CheckBoxes, when a CheckBox in that row is (de)selected. That logic will act as a gatekeeper to enforce your rules as to the number of selections allowed on a row.

More to follow.

Lewis

Hi John,

I reworked my code to be able to do the following:
a. Create CheckBoxes
b. Verify CheckBox Selection:
(1) Only allow one Primary Selection
(2) Allow 3 or less Alternate Selections
c. Process Sheet 'Output_Export_Report' and put results on Sheet 'Image Selections'.

See the attached .zip file which contains my .xlsm file.

Lewis

Lewis,

Thank you very much. This is great and I have learned quite a bit. I apologize for the delay in responding. In my absent minded state, I did not realize that this forum thread had continued to page two so I was still waiting for your "More to come". LOL too funny. Thank you again for all your help!

I did not realize that this forum thread had continued to page two

Very strange. My computer is still on page 1.

----------------------

John, I'm glad everything worked out for you. If you have any more problems and or questions please feel free to ask.

Lewis