+ Reply to Thread
Results 1 to 9 of 9

if then loop works incorrectly - syntax?

  1. #1
    Registered User
    Join Date
    02-25-2013
    Location
    BSB, Bruei
    MS-Off Ver
    Excel 2007
    Posts
    5

    if then loop works incorrectly - syntax?

    I am using the following code to hide and unhide specifc worksheets within a single macro, once I have it working I will seperate the hide and unhide

    to unhide worksheets ("RFQ") is never hidden so I want to skip by that sheet

    Dim ws As Worksheet (declared once for both Loops)

    For Each ws In ActiveWorkbook.Worksheets
    If InStr("RFQ Supplier", ws.Name) = 0 Then
    ' your code here

    ws.Visible = xlSheetVisible

    End If
    Next ws

    the following code will hide all the sheets but one and return an error

    I want to hide worksheets except ("RFQ") but that is not happening

    For Each ws In ActiveWorkbook.Worksheets
    On Error Resume Next (inserted this and it seems to resolve my error window - likely you can't hide all the sheets in a workbook)
    If InStr("RFQ Supplier", ws.Name) = 0 Then
    ' your code here

    ws.Visible = xlSheetVeryHidden

    End If
    Next ws


    Have tried a few alternatives but going around in circles at present, used a
    any suggestions would be appreciated Thanks

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: if then loop works incorrectly - syntax?

    I am confused actually what you are trying to do? can you please explain instead of showing your code


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: if then loop works incorrectly - syntax?

    If you want to hide all worksheets in your workbook except the one called "RFQ Supplier" the you could use the following code

    Please Login or Register  to view this content.
    If you like my contribution click the star icon!

  4. #4
    Registered User
    Join Date
    02-25-2013
    Location
    BSB, Bruei
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: if then loop works incorrectly - syntax?

    I am working on a basic macro to do the following.
    NOTE: this will become two macros once I have it working

    1. unhide all very hidden sheets in a workbook
    2. unprotect all sheets in a workbook
    3. unlock all cells in a workbook
    4. then select a range on a worksheet
    5. lock the selected range
    6. select a named range
    7. unlock the named range
    8. select a named range 2
    9. hide rows in named range 2
    10. protect all the worksheet
    11. hide all sheets in workbook except specified sheet

    presently I have it all working except for #11 which is ignoring the argument in the if statement?

    this is until I add passwords and save as name pulling numbers from a list (but thats tomorrow)

    Cheers
    Last edited by canoehack; 02-25-2013 at 06:50 AM. Reason: missing part of concept

  5. #5
    Registered User
    Join Date
    02-25-2013
    Location
    BSB, Bruei
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: if then loop works incorrectly - syntax?

    Hi Ollie, tried your code but its doing what my other example did, It does not skip over the "RFQ" sheet and hides it.

    digressing a bit but
    Public in your code is so this routine can be called from outside?
    and the line ws.Visible = (ws.name = "RFQ Supplier") when the string in brackets is = what value is returned vs when not =

    newbee questions. Cheers

  6. #6
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: if then loop works incorrectly - syntax?

    Quote Originally Posted by canoehack View Post
    It does not skip over the "RFQ" sheet and hides it.
    That is not really possible, unless the worksheet is not named "RFQ Supplier". Basically the bit "(ws.Name = "RFQ Supplier")" returns TRUE is the name does equal RFQ Supplier (thus setting visible to TRUE for this worksheet), or it returns FALSE for any other name and thus setting visibility to false for all other worksheets.


    Quote Originally Posted by canoehack View Post
    Public in your code is so this routine can be called from outside?
    What do you mean called from outside? If you mean from outside Excel, then NO. Public just means it can be called from any routine in any other module.

  7. #7
    Registered User
    Join Date
    02-25-2013
    Location
    BSB, Bruei
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: if then loop works incorrectly - syntax?

    Hi Ollie,

    I have it working, I renamed the worksheet.. perhaps there was an extra space etc.
    are you familiar with the Sheets() i.e. codename and how it could be used in
    place of the ws.name thanks for the quick reply

  8. #8
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: if then loop works incorrectly - syntax?

    The .CodeName property is a "internal name" that you can assign to a worksheet, but unlike the normal name you cannot use CodeName to refer to a worksheet directly

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    02-25-2013
    Location
    BSB, Bruei
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: if then loop works incorrectly - syntax?

    Thanks Ollie, this macro adventure is a bit like the solid modeling I usually do, full of suprises. appreciate the quick response and have got my macro working. Passwords and Save as details are for tomorrow. I ended up renaming the worksheet in question it looked identical to the code but perhaps there was an extra space etc. hence my question re the .codename property

    cheers
    Last edited by canoehack; 02-25-2013 at 09:33 AM. Reason: reason for solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1