• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Select Boxes on Extracted Data using VBA

S

sammyp1

New Member
Hello all,

First time site user.

Would greatly appreciate it if someone could show me how to insert a select box and a corresponding linked cell (indicating if checked or not checked) to each datapoint extracted from a dynamic range table.

The attached file (found on another site) is a good example on how I envision my final product to look like. I simply need select boxes (aligned center regardless of the height differences in each row) in the cell to the right of the extracted data found on the first worksheet. In this example, the boxes would be located in Column H starting in Cell H9.

Eventually, I plan to extract all those datapoints selected via the select box to a word document.

I'm not too comfortable in writing code, so any assistance would helpful.

Thank you,
Sam
 

Attachments

  • Database VBA Test.xls
    53.5 KB · Views: 4
Rather than building boxes, linking them to a cell, and keeping track of dynamic generation, I think if you just put a mark (X or Y perhaps?) in the cell, then you have many more options, such as using ADvancedFilter (which is how the example workbook works), or just a regular AutoFilter to get to the data you want.
 
Luke, thank you for responding.

With the check box, the user will have the opportunity to select what extracted rows they'd like to export to Word. Your idea would work if the assumption was that all extracted rows from the dynamic range table was to exported.

I hope this makes sense.
 
Sorry, perhaps I wasn't clear. In the dynamic output in XL, have another column so user can mark their choices.
upload_2014-7-8_14-23-22.png
Then you can use any of the methods in previous post to filter down/extract the rows that have been marked.

If you want the choices cleared after dropdowns are changed, you can modify the code in the wksSel module. Near the beginning change this:
Code: 
Application.EnableEvents = False
to this:
Code: 
Application.EnableEvents = False

Range("H9:H100").ClearContents
 
Luke, what Narayan has presented is exactly what I'm looking for. I appreciate your interest in assisting me.

Narayan, could you briefly explain what you did? What code, formulas, etc were inserted. I'd like to incorporate this into another file that has only 3 columns of datapoints (starting from B7 to D7) and has approximately 200 rows of datapoints.

Also, I'm noticing that any checked boxes after Cell H9 are not coming up as "Selected" on Column J.

Thank you.
 
Hi Sammy ,

The cumbersome part is inserting the checkboxes , and putting their LinkedCell property to the corresponding cells in column I ; thus for the checkbox in cell H9 , the LinkedCell property specifies I9 , for the checkbox in cell H10 , the LinkedCell property specifies I10 , and so on. This will have to be done manually , or code will have to be written to do it as a one-off exercise.

I have introduced the formulae in column J , just to let the user know that a particular item has been selected , since the checkbox itself is quite small.

The code has been written so that if you select either a category or an actor from the dropdowns , and the number of items on screen reduces , then the extra checkboxes are unchecked and made invisible , since it will otherwise look odd if there is no item in a row , and yet the checkbox remains , along with the status.

Narayan
 
Kudos to @NARAYANK991 for getting through that cumbersome part. Knew it could be done, but wasn't sure how to keep everything straight. :)
 
Yes, definitely kudo to NarayanK991.

Narayan - I am however having a slight problem in incorporating your code into my work. Compared to the sample file, my datapoints are captured from B8:D94 and have inserted Active X checkboxes down column F (with column E being a spacer between my data and checkbox). For each checkbox, I removed the caption name, linked the checkbox to corresponding cell in column G (example: checkbox residing over Cell F8 is linked to Cell G8, Cell F9 to Cell G9 and so on). This is, in effect, should allow me to remove the following line from you code:

Code: 
Range("B" & i).Offset(, 7).Value = False

In addition, I've changed the vbNullstring to reference Range ("C"& i) instead of ("B" & i). With the changes I've made, I'm still coming across an error with the following line

Code: 
With Me.OLEObjects(i - 8)

I adjusted the location of the OLEObjects to (i-6) as I assume this references where my checkbox resides (column F). What am I overlooking? How does the application know what residing cell is linked to ActiveX checkboxes?

Again, being somewhat to new I would greatly appreciate your assistance once more.

Thank you,
Sam
 
Hi Sam ,

You need to ensure that your checkboxes are numbered sequentially ; if you see your original sample file , the checkboxes are numbered Checkbox1 through Checkbox8 ; however , more important is the fact that they are the objects numbered 1 through 8 on the worksheet ; since we are referring to them using the OLEObjects object , if you have other objects on the worksheet , they will also be accessible using the OLEObjects object.

To take your real data , suppose you have checkboxes in rows 8 through 94 i.e. 87 checkboxes ; these should be numbered sequentially , and placed sequentially , so that Checkbox2 is just below Checkbox1 , Checkbox3 is just below Checkbox2 ,....

Now , if you do have other OLEObjects on the worksheet , you will need to exclude them from being processed within the code. Suppose Checkbox1 is actually OLEObject 5 , Checkbox2 is actually OLEObject 6 ,....

Since your row is from 8 onwards , when i = 8 , it should refer to OLEOBject 5 , in which case you need to subtract 3.

Suppose you do not have other OLEObjects on your worksheet , or if the other OLEObjects are all numbered after the checkboxes , then your Checkbox1 is OLEObject1 , Checkbox2 is OLEObject2 and so on. In this case , you subtract 7 from i to get the index number of the OLEObject.

Narayan
 
Narayan - That was my problem....I checkboxes were not numbered sequentially. That solves my problem. I may even consider using your "Selected" features as the checkboxes are indeed small, and in several instances based on the contents in the rows are lumped up very close to one another.

Let me ask you, is there a quick way to put a conditional formatting procedure (such as highlight rows B8 to D8) if checkbox1 in cell F8 has been clicked. Just curious.

Again, thank you.
 
Hi Sam ,

Certainly ; select your data range first ; in your first uploaded file , this is the range B9 : G16.

For the CF formula , just use :

=$I9

Put in a colour of your choice. That's it.

When ever a checkbox is checked , the entire row of data will be coloured. This assumes that the LinkedCell properties of the checkboxes are all assigned to the cells in column I.

Narayan
 
Perfect.

One thing I do notice is every time I open up my workbook and select either a dropdown category or type in a selected word, the checkboxes have a slight delay in reacting (whether to be visible or invisible). They work properly but are not in sync with the data being filtered. Anyway I can remedy this?

Thank you.
 
Hi Sam ,

You can include the following 2 statements in the code :

Application.ScreenUpdating = False

Application.ScreenUpdating = True

See the attached file.

Narayan
 

Attachments

  • Database VBA Test.xls
    78 KB · Views: 10
It works beautifully. I can't thank you enough Narayan for your assistance. I have a few other things I'm working on for my database that I hope to solve by this weekend. If I have any issues, I now know a site to get some help.
 
Hello Narayan,

Hope you can spare the time to help assist me once more. It's simply trying to address my previous issue for the next step.

For the past two days, I've been struggling to come up with a way to link the individual checkboxes to specific data rows that are extracted. This is because based on what is selected by the user, a checkbox representing a range B9:G9 of a specific range will vary. For example, if there is no selection then checkbox 1 will represent the data range that contains ShawShank Redemption. However, if you select John Travolta, checkbox 1 will represent the data range for Pulp Fiction. I can never have the checkbox1 call another specific procedure because of the multiple variabilities. In the end, what I'm trying to do is combine word documents (saved elsewhere) based on the selections extracted. These selections will simply be headers for detailed summaries and reports.

Any suggestions would be greatly appreciated. Thank you.
 
Hi Sam ,

Using VBA , we can always retrieve the information in the row in which the checkboxes have been checked ; if you can indicate what is to be done with the information , the needed code can be written.

Narayan
 
I know it could be possible, but it's a bit frustrating for someone like me who's relatively new to VBA.

Simply, I have some 75 rows that contain questions and answers each with a checkbox. These questions and answers have individual Word documents that go into the answers in more detail (about 1 -3 pages in length) including tables and charts.

Based on the filter (either by a drop down category or word search), the user will have an opportunity to select (with the use of checkboxes) what extracted questions and answers they would like to view further. By selecting their checkboxes, I'd like the Word documents to merge into one file for the user to peruse. This new file will combine the details of their selected questions and answers on one file (instead of looking them up in each currently saved files).

Below is my initial attempt on how the Word documents could potentially merge. Of course, this does not take into consideration what questions and answers have been selected from the boxes checked.

Code: 
Sub test()
   
  With GetObject("H:\1.SampleWordMerge\1.question1.docx")
        .Content.InsertAfter GetObject("H:\1.SampleWordMerge\2.question2.docx").Content
        .Content.InsertAfter GetObject("H:\1.SampleWordMerge\3.question3.docx").Content
        .Content.InsertAfter GetObject("H:\1.SampleWordMerge\4.question4.docx").Content
        .SaveAs2 "H:\1.SampleWordMerge\Selected Q and As" & Format(Now(), "_mm_dd_yyyy hh mm ss") & ".docx", 12
        .Close False
    End With
End Sub

I hope this makes sense.
 
Hi Sam ,

What next ?

As far as I can see , instead of a straightforward .InsertAfter statement , you need to use an IF statement to add the content to the master Q and A file only if the corresponding checkbox was checked.

Can you say how the checkboxes are related to the questions ?

This application seems to be different from your initial post.

Narayan
 
Narayan, I certainly apologize if I'm not explaining this properly.

All I'm trying to do is identify what specific rows from the master database were checked so I then can have the corresponding word files (that contain more details regarding the questions and answers) merged together into one new comprehensive file. I would have imagined the use of checkboxes could have solved my problem, but the boxes are not linked 1 to 1 to a specific datapoint (an example from the file would be checkbox1 to the Shawshank Redemption/Tim Robbins row). If it was, I could easily write case statements that would trigger the appropriate word files based on the checkbox number selected.

To answer your questions, the checkboxes should allow me to identify what rows are being asked by the user for a deep dive of the questions and answers. This particular issue is my next step to completing a personal project I'm working. I'm sorry if the current thread has moved on to another issue. I will certainly end it if you like, and resubmit.

Thank you.
 
You must log in or register to reply here.
Back
Top