• Hiding cell contents (1)

    Author
    Topic
    #386162

    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

    Viewing 0 reply threads
    Author
    Replies
    • #668937

      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

      • #668943

        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

        • #668956

          Strange, worked fine for me.

          Try changing the takefocusonclick property of the checkbox to false

          • #669196

            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

            • #669201

              Ah, the light has come on smile.

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

            • #670290

              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

            • #670291

              Did you try setting the TakeFocusOnClick property to false?

            • #670299

              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

            • #670302

              Oh yes, I forgot.

            • #670304

              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

            • #669205

              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

            • #669207

              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

    Viewing 0 reply threads
    Reply To: Hiding cell contents (1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: