Hiding cell contents (1)

Topic

New Reply

Hi all,
A Workbook consists of 8 sheets of which Sheet1 is used for collecting data.
The remaining 7 sheets all have the same layout. When hiding specific cells on sheet2 the same cells for the other 6 sheets should be hidden.
The following macro works for sheet2, enters the other sheets but does not change the cell formating.

Private Sub CheckBox1_Click()
‘
Dim shSheets As Sheets
Dim wks As Worksheet
Dim rng2 As Range

Set shSheets = Worksheets(Array(2, 3, 4, 5, 6, 7, 8))
For Each wks In shSheets
Set rng2 = Range(“F9,F10,F16,F17,F18,F19,F20,F21,F22,F23,F29,F30,F33,F34,F35,F36,F38,F39,F40”)
If Range(“F2”) = True Then
rng2.Select
Selection.NumberFormat = “;;;”
Else:
Selection.NumberFormat = “#,##0.000” ‘CUUBS
End If
wks.Range(“a1”).Value = 100
wks.Range(“a1”).Font.Bold = True
Next wks
End Sub

What am i doing wrong?
TIA
Joop

Reply | Quote

Replies

Hi,

[indent]

---

What am i doing wrong?

---

[/indent]

You didn’t specify the sheet the range applies to. When omitted, XL assumes you meant to use the active sheet.

Change your code to this:

Private Sub CheckBox1_Click()
‘
Dim shSheets As Sheets
Dim wks As Worksheet
Dim rng2 As Range

Set shSheets = Worksheets(Array(2, 3, 4, 5, 6, 7, 8))
For Each wks In shSheets
with wks
Set rng2 = .Range(“F9,F10,F16,F17,F18,F19,F20,F21,F22,F23,F29,F30,F33,F34,F35,F36,F38,F39,F40”)
If .Range(“F2”) = True Then
rng2.NumberFormat = “;;;”
Else:
rng2.NumberFormat = “#,##0.000” ‘CUUBS
End If
.Range(“a1”).Value = 100
.Range(“a1”).Font.Bold = True
end with
Next wks
End Sub

Reply | Quote

Hi Jan Karel,

Thanks for the fast response.
When i run your code i get a RTE 1004 / Unable to set the NumberFormat property of the Range class.
on the line rng2.NumberFormat = “;;;”
Any idea?
TIA
Joop

Reply | Quote

Strange, worked fine for me.

Try changing the takefocusonclick property of the checkbox to false

Reply | Quote

Hi Jan Karel,

When using Excel 2000 all works OK.
However as not all users use Excel 2000 this has to work for Excel97 as well.
Should i duplicate this code for each sheet or is there another way?
Can you point me in the right direction?

TIA
Joop

Reply | Quote

Ah, the light has come on .

Put the code in a NORMAL module and call that sub from the click event of the checkbox.

Reply | Quote

Hi again,

Problem is not solved yet.
The normal module runs OK. However the error message remains same when using the checkbox. When changing the value from the checkbox’s linked cell, by typing, from TRUE to FALSE and back, the code runs OK. Also the checkbox is marked/unmarked.
Any suggestions?

TIA
Joop

Reply | Quote

Did you try setting the TakeFocusOnClick property to false?

Reply | Quote

In XL97 I don’t think it has a takefocusproperty available.

Try SELECTING something else:

Activecell.select

to remove the focus from the checkbox. Many of the tasks in XL97 can NOT be done when a control toolbox item has the focus. Most do NOT have the takefocusonclick property and this “selecting” something else seems to work.

Steve

Reply | Quote

Oh yes, I forgot.

Reply | Quote

Hi all,

Now it works.
Steve, I’ll try to keep remark regarding the focus in mind. According to the help function takefocusonclick is only available for the commandbutton.
Thanks a lot.

Joop

Reply | Quote

Also, in addition to Jan’s suggestion, if the formatting of all sheets depends on F2 in the sheet with the checkbox, remove the dot in red from the following line

If .Range(“F2”) = True Then

That should look after all the sheets.

Andrew C

Reply | Quote

Jan Karel,

Putting the code in a normal module works.
Thanks very much.

Andrew,
Thanks for the response; will try your suggestion later.

Thanks
Joop

Reply | Quote