Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Hiding cell contents (1)

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding cell contents (1)

    Hi,

    <hr>What am i doing wrong?<hr>

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Hiding cell contents (1)

    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

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding cell contents (1)

    Strange, worked fine for me.

    Try changing the takefocusonclick property of the checkbox to false
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Hiding cell contents (1)

    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

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding cell contents (1)

    Ah, the light has come on <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

    Put the code in a NORMAL module and call that sub from the click event of the checkbox.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hiding cell contents (1)

    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 <font color=red>.</font color=red>Range("F2") = True Then

    That should look after all the sheets.

    Andrew C

  8. #8
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Hiding cell contents (1)

    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

  9. #9
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Hiding cell contents (1)

    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

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding cell contents (1)

    Did you try setting the TakeFocusOnClick property to false?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hiding cell contents (1)

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

    Try SELECTING something else:
    <pre>Activecell.select</pre>

    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

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding cell contents (1)

    Oh yes, I forgot.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Hiding cell contents (1)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •