100 tables in Word

[ReneY]

Hi everyone!

I'm in a big problem, I'm an intermediate programmer and I've been assigned to a huge task regarding VBA, Word and 100 tables.... let me explain a bit further:

I work in a big company and the department where I work offers about 500 products, all this products are placed in one single Word 2003 file, the file itself is 30 pages long, and divides the 500 products in different groups by tables, there are tables with only 2 rows and there are tables with up to 18 rows; each one of the rows has the product description and a checkbox. Alltogether there are 100 tables of products. Once a salesperson sells a product (or several) this big file is handed to both the customer and the production department with the sold/bought product's checkbox checked.... the problem arises when the customer buys only ONE product of the 500 hundred, both the customer and the production department still get the 30 pages only with one product highlighted, so its a bit of a hazle sometimes to even find the product that has been sold in this 30 pages. The idea of this program is to simplify the handling of the Product Specification Sheet (that's how the file is called) and to be able to easily see which products are of relevance to the seller/buyer in a couple pages instead of seeing ALL products EVERY time and have to go through ALL the 30 pages....

What I've done is to program all the checkboxes and a toggle button, when it is active all the rows/checkboxes that are not checked are hidden, and if the toggle button is unactive, then all rows all visible again.


Here's an example of the first table (that actually works great)


'******TABLE1*********
If ToggleButton1.Value = True Then
If ActiveDocument.CheckBox1.Value = False Then
ActiveDocument.Tables(1).Rows(1).Range.Font.Hidden = True
End If
If ActiveDocument.CheckBox2.Value = False Then
ActiveDocument.Tables(1).Rows(2).Range.Font.Hidden = True
End If
If ActiveDocument.CheckBox3.Value = False Then
ActiveDocument.Tables(1).Rows(3).Range.Font.Hidden = True
End If
If ActiveDocument.CheckBox4.Value = False Then
ActiveDocument.Tables(1).Rows(4).Range.Font.Hidden = True
End If
If ActiveDocument.CheckBox5.Value = False Then
ActiveDocument.Tables(1).Rows(5).Range.Font.Hidden = True
End If
End if

If ToggleButton1.Value = False Then
ActiveDocument.Tables(1).Rows(1).Range.Font.Hidden = False
ActiveDocument.Tables(1).Rows(2).Range.Font.Hidden = False
ActiveDocument.Tables(1).Rows(3).Range.Font.Hidden = False
ActiveDocument.Tables(1).Rows(4).Range.Font.Hidden = False
ActiveDocument.Tables(1).Rows(5).Range.Font.Hidden = False
End If
End Sub

It works perfectly for the first 50 tables, the problem is that I
have to specify which table and which row i want the checkbox to hide
and show (Tables(1).Rows(n)....... Tables(2)Rows(n)....... ...Tables(100)Rows(n)....... !!!) and that is like 30 pages of programming!

Even though I did it the "long way", that is, writting each and every
single one of the codes for all the 500 checkboxes and 100 tables... it does not work now because the "procedure is too long"... and I dont know how to shorten it! Someone suggested me to use bookmarks and now all my tables have bookmarks, but i will still have to program every bookmark i think and it still would be a lot of work and I dont know how to modify a table with bookmarks anyway, only text.

Another big issue is that all the tables are different sizes, if they had all the exact same number of rows and columns I could probably use a loop, but they vary depending on the products.

I would like to ask you if someone could please help me with an easier and shorter way to program this; I'm sure it could be done with variables, but I have never done anythin so complicated with variables.

Thanks for your help in advance

ReneY


Edit Lucas: VBA Tags added to code. ReneY, If you select your code when posting and hit the vba button it will be formatted as above.

[lucas]

Wow, 500 activeX checkboxes? This is not what Word is for. You should be using Excel or Access. This would be a simple problem.


That being said, are all of the checkboxes in a column? A or 1?

Another idea would be to check all of the checboxes with one or two lines of code and make changes according to whether they are true or not:


Dim oCtl As Control
For Each oCtl In Me.Controls
If TypeOf oCtl Is msforms.CheckBox Then
If Me.Controls(oCtl.Name).Value <> True Then


Not sure if you are using msforms type checkbox or not.....you will have to make sure of that......

[lucas]

Or maybe something like this to loop through all the checkboxes:

Option Explicit
Sub checked() Dim oCB As OLEObject
With Sheets("Sheet1")
For Each oCB In .OLEObjects
If TypeName(oCB.Object) = "CheckBox" Then
If oCB.Object.Value = True Then
MsgBox oCB.Name & " is checked"
End If
End If
Next oCB
End With
End Sub

[fumei]

1. I have to agree with Steve in that 500 ActiveX controls seems...heavy.

2. Demo attached. I used shortcut keys, but you could have other ways to execute. Attaching it to the Save command perhaps. Or a button on the toolbar. Whatever.

NOTE:! You must not have a control selected. In other words, the Selection must NOT be on a control. This code will not run if the selection is on a control.

So check some boxes, move the selection somewhere, it does not matter where.

Alt-j will hide all rows that do NOT have a checked box.

For convenience, I added Alt-u. This clears everything. All checkboxes are unchecked, all rows are not Hidden.

Here is the code.
Sub HideStuff()
' alt-j
Dim oForm As InlineShape
Dim r As Range

For Each oForm In ActiveDocument.InlineShapes
If oForm.Type = wdInlineShapeOLEControlObject Then
Set r = oForm.Range
r.Expand Unit:=wdRow
If oForm.OLEFormat.Object.Value = False Then
r.Font.Hidden = True
Else
r.Font.Hidden = False
End If
End If
Next
End Sub


Sub yadhkll()
' alt-u is shortcut
' explicitly clears everything
' checkbox are blank, rows are visible
Dim oForm As InlineShape
Dim r As Range

For Each oForm In ActiveDocument.InlineShapes
If oForm.Type = wdInlineShapeOLEControlObject Then
oForm.OLEFormat.Object.Value = False
Set r = oForm.Range
With r
.Expand Unit:=wdRow
.Font.Hidden = False
End With
End If
Next
End Sub

[ReneY]

Thanks a lot for your help! Sorry for the late reply but I went on vacations.
Your code was exactly what I needed! Thanks!

Best rgds

[lucas]

Reney, be sure to mark your thread solved using the thread tools at the top of the page.