Click here to Skip to main content
11,492,385 members (61,707 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Excel
How to use this below code ?
i want to select case



Sub SelectedWoksheets()
'http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
Dim ws As Worksheet
 
    For Each ws In ActiveWindow.SelectedSheets
        With ws
        
            'With Code Here
        
        End With
    Next ws
End Sub

[Edit]Code block added[/Edit]
Posted 3-Nov-12 6:33am
tcchean308
Edited 3-Nov-12 6:50am
v3
Comments
Richard MacCutchan at 3-Nov-12 12:18pm
   
What do you mean you want to select case? In order to do what exactly? Please edit your question and explain what you are trying to do.
ridoy at 3-Nov-12 16:20pm
   
totally unclear,elaborate it..

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

If i understand you well, you want to use Select Case ... End Select statement.
Select case ws.Name
    Case "Sheet1"
        'do something
    Case "Sheet2"
        'do something
    Case "Sheet3"
        'do something
    Case Else
        'do something
End Select

But if would like to go through the collection of worksheets to "find" the correct one by its name, better use something like this:
Function GetWoksheet(wshName As String, wbk As Workbook) As Worksheet
Dim wsh As Worksheet
 
On Error Goto Err_GetWoksheet
 
Set wsh = wbk.Workseets(wshName)
 
Exit_GetWoksheet:
    Set GetWoksheet = wsh
    Exit Function
 
Err_GetWoksheet:
    MsgBoxErr.Description, vbExclamation, Err.Number
    Set wsh = Nothing
    Resume Exit_GetWoksheet
 
End Function

Usage:
Set wsh =  GetWoksheet("Sheet4", ActiveWorkbook)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 404
1 Maciej Los 266
2 KarstenK 220
3 Sascha Lefèvre 215
4 CPallini 208
0 Sergey Alexandrovich Kryukov 9,943
1 OriginalGriff 8,610
2 Sascha Lefèvre 3,609
3 Maciej Los 3,372
4 Richard Deeming 2,440


Advertise | Privacy | Mobile
Web01 | 2.8.150520.1 | Last Updated 4 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100