|
Subject:
|
VLookup, arrays, and worksheet names
|
|
Posted By:
|
chp
|
Post Date:
|
4/7/2006 3:15:28 PM
|
I am setting up access restrictions to the active workbook based on data contained in another closed workbook ("MgmtAccess") that contains employee number and tab names of the worksheets they can access, as well as which one is their "home" sheet. I've come across multiple problems and I'm going around and around with the best way to do this and would like some guidance on what logic to use. I've got little snippets of code for various things but can't get them all together. I'm fairly certain I'm making this harder than it needs to be.
I've tried to use the VLookup function so I didn't have to bother with making an array, but get errors (see FindHome below). This sub will take the user to the worksheet where their employee number is and activate/select that section (it runs after I've already unlocked all the applicable worksheets).
I've tried it with and without "WorksheetFunction" inserted between "Application" and "VLookup". For the code below, the error I get is "application-defined or object-defined error" (run-time error 1004) when it gets to "Set HomeSheet =". I've tried it with HomeSheet.Name and get "invalid qualifier".
Sub FindHome(EmpNum)
'Find home worksheet for manager and set active cells
Dim AccessWB As Range
Dim HomeSheet As Worksheet
Set AccessWB = Workbooks("MgmtAccess.xls").Worksheets("Sheet1").Range("A2:D17")
Set HomeSheet = Application.VLookup(EmpNum, AccessWB, 3, False)
ThisWorkbook(HomeSheet).Cells.Find(What:=EmpNum).Activate
Range(ActiveCell, ActiveCell.Offset(0, 20)).Select
End Sub
Because there seem to be issues using the tab name of a worksheet (although I can't tell how much of a problem until I get to the next line of code above), I created a procedure to create an array that has the sheet# and the corresponding sheet name for all sheets in the active workbook so I could translate HomeSheet into an indexed reference (sheet x). Creating the array works fine...although I'm not quite sure how to refer back to that to get the sheet# back out. Perhaps this is because it's late in the week/day or I'm missing some knowledge about arrays or probably a bit of both!
If I can figure this out, I should be able to use the same principle to get the list of worksheets from MgmtAccess that they can access and get those open.
One glitch is that the cells that list the worksheet names provides a series of names separated by commas. I'm afraid I'm going to have to reformat that worksheet so I only have one entry per cell, but thought I'd ask if anyone knew a way to easily parse through that information to get the data out.
Once I get those values (and translate them to indexed names if I need to), I should be able to do something like the following where WSlist is an array containing the worksheet tab names listed in that cell.
For Each WS In WSlist
WS.Visible = xlSheetVisible 'Open worksheets for editing
WS.Protect Contents:=False
Next
I know this is a lot of stuff, and it may be difficult for you to put it together out of context. I took programming in college back when Pascal was the latest and PCs were a thing of the future and now I'm just getting some trial-by-fire exposure to VBA. That gives you a little background on why I'm so lost on this one!
Any guidance you can provide (and you've always bailed me out in the past), will be greatly appreciated.
--Cinda
|
|
