set worksheet variable based on user input 1

[JoeMicro]

HI:
The user selects a value from a list, the code will then lookup the value on another sheet which has an offset with the sheet name, and sets the variable to that sheet.

Dim vOurResult As Range
        With Sheet12.Range("SheetName")
            Set vOurResult = .Find(what:=Sheet1.Range("E25"), After:=.Cells(1, 1), _
                LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)
        End With
     
    If Len(Sheet1.Range("E25")) > 0 Then
        Set shAccount = vOurResult.Offset(0, 1)
    End If

but then i get a 'Type Mismatch' Error highlighting

[COLOR=red]Set shAccount = vOurResult.Offset(0, 1)[/color]

when i hover over the "Set Shaccount" it says "Nothing"
but the "Vouresult.offset(0,1)" does Give me Sheet5

Thanks

 

[combo]

Assuming that sheet's name was found (in opposite to code names you use for searching):
Set shAccount = Worksheets(vOurResult.Offset(0, 1))

combo

 

[JoeMicro]

Thanks combo For your Reply.

but i tryed it already, doesn't work, gives me 'type mismatch', and when i hover over it, i also get <Type Mismatch>

Thanks

 

[xlbo]

what is shAccount defined as ?

If vOurResult.offset(0,1) does indeed = "sheet5"

then Combo's response should work as long as you have

dim shAccount as worksheet

in your code....



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Rofeu]

Try:

Set shAccount = Thisworkbook.Sheets(vOurResult.Offset(0, 1).Value))

Cheers,

Roel

 

[JoeMicro]

Thanks for your Replies

shAccount is defined as

Public shAccount As Worksheet

and Roel i tried your suggestion and i get 'Subscript Out Of Range'

Thanks

 

[xlbo]

'Subscript Out Of Range'

would tend to indicate that you don't have a sheet named "Sheet5" in the workbook that the code is being run from.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JoeMicro]

That's true, my sheet is named something else, but in my code i wanted to put the generic sheet name in case the user wants to change the sheet name.

but i do have a generic sheet name "sheet5

 

[SkipVought]

"...i wanted to put the generic sheet name in case the user wants to change the sheet name."

The Sheet Object has a NAME property that can be change from the Sheet Tab, and the CodeName property that can ONLY be changed in the VB Editor or via code. I almost always use the Sheet.CodeName property when referencing sheets in code.


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[combo]

If you plan to access worksheet by its code name you can either loop through all sheets and test code name or:

Dim shAccount As Worksheet, strCodeName As String, strName As String
strCodeName = "Sheet5"
With ThisWorkbook
    strName = .VBProject.VBComponents.Item(strCodeName).Properties("Name")
    Set shAccount = .Worksheets(strName)
End With

The code requires programmatic access to vb project for xp+ office versions (security settings).


combo

 

[xlbo]

...that would be the problem then !!! You can't reference the codename of a worksheet like that

Combos suggestions (again) should work...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JoeMicro]

Thanks Combo that Did the job i implemented the your code into mine as follows

If Len(Sheet1.Range("E25")) > 0 Then
     Dim vOurResult As Range
        With Sheet12.Range("SheetName")
            Set vOurResult = .Find(what:=Sheet1.Range("E25"), After:=.Cells(1, 1), _
                LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)
        End With
        Dim shAccount As Worksheet, strCodeName As String, strName As String
        strCodeName = vOurResult.Offset(0, 1).Value
        With ThisWorkbook
            strName = .VBProject.VBComponents.Item(strCodeName).Properties("Name")
            Set shAccount = .Worksheets(strName)
        End With
    End If

and it does the job

Thanks
Joe