Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just want to minimize the code, however it is working fine (Thanks to lounge). can be ignored if wasting time..

    Col. are common in all sheets.
    Attached Files Attached Files
    Regards
    Prasad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this (always test on a copy of your workbook):

    Code:
    Sub hidesc()
      Dim strNameAs Variant
      For Each strName In Array("BG-3 (ALL)", "BG-3 (WO ANS & ANTB)", _
    	"COMPONENT", "SSC", "EXHAUST", "RIM", "HRD", "PNG", "ANS", "ANTB")
    	With Worksheets(strName)
    	  .Columns("A:AG").Hidden = True
    	  .Columns("AI:AI").Hidden = True
    	  .Columns("AX:AX").Hidden = True
    	  .Protect Password:="psd"
    	End With
      Next sht
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783352' date='07-Jul-2009 14:17']Try this (always test on a copy of your workbook):

    Code:
    Sub hidesc()
      Dim strNameAs Variant
      For Each strName In Array("BG-3 (ALL)", "BG-3 (WO ANS & ANTB)", _
    	"COMPONENT", "SSC", "EXHAUST", "RIM", "HRD", "PNG", "ANS", "ANTB")
    	With Worksheets(strName)
    	  .Columns("A:AG").Hidden = True
    	  .Columns("AI:AI").Hidden = True
    	  .Columns("AX:AX").Hidden = True
    	  .Protect Password:="psd"
    	End With
      Next sht
    End Sub
    [/quote]
    fine with slight changes
    Sub hidesc()
    Dim strName As Variant
    For Each strName In Array("BG-3 (ALL)", "BG-3 (WO ANS & ANTB)", _
    "COMPONENT", "SSC", "EXHAUST", "RIM", "HRD", "PNG", "ANS", "ANTB")
    With Worksheets(strName)
    .Columns("A:AG").Hidden = True
    .Columns("AI:AI").Hidden = True
    .Columns("AX:AX").Hidden = True
    .Protect Password:="psd"
    End With
    Next
    End Sub
    What goes wrong If I add .Range("AH1").Select in code before end with.
    Regards
    Prasad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can only select a cell in the active sheet, not in other sheets. So if you really want to select cell AH1, you have to add 2 lines:

    .Select
    .Range("AH1").Select

    The first line activates the sheet, the second cell AH1 on that sheet.

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783465' date='08-Jul-2009 07:08']You can only select a cell in the active sheet, not in other sheets. So if you really want to select cell AH1, you have to add 2 lines:

    .Select
    .Range("AH1").Select

    The first line activates the sheet, the second cell AH1 on that sheet.[/quote]
    amazing. Thanks
    Regards
    Prasad

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='783468' date='08-Jul-2009 07:32']amazing. Thanks[/quote]
    How can I assign this macro to button? I have inserted a button in one sheet & tryign to assign but no such option is displays. I have done this before but dont remember how.
    Regards
    Prasad

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you want a button on a worksheet:
    - Display the Forms toolbar (for example View | Toolbars | Forms).
    - Click on the Command Button button
    [attachment=84600:x.png]
    - Click on your worksheet.
    - Excel will ask you to assign a macro.
    Attached Images Attached Images
    • File Type: png x.png (3.7 KB, 3 views)

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783470' date='08-Jul-2009 08:06']If you want a button on a worksheet:
    - Display the Forms toolbar (for example View | Toolbars | Forms).
    - Click on the Command Button button
    [attachment=84600:x.png]
    - Click on your worksheet.
    - Excel will ask you to assign a macro.[/quote]
    I have already inserted a Command Button button in ws. The problem is that excel is not asking to assign any macro on it. I have tried the same with new WB but no results. ?
    Regards
    Prasad

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Did you use the Forms toolbar or the Control Toolbox toolbar?

  10. #10
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783474' date='08-Jul-2009 08:28']Did you use the Forms toolbar or the Control Toolbox toolbar?[/quote]
    I am sorry Hans, I was using Control Toolbox instead of Forms Toolbr. Now everything is fine. Thanks a lot.
    Regards
    Prasad

Posting Permissions

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