LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
Posts: 1
Default Using very hidden macros (plus other macros) on a shared file

Hi all

I am trying to produce a shared workbook for the weekly payroll, which needs to be accessed by various units, but for them to only have access to the tabs which relate to their units. Due to tight deadlines, they are all likely to be working on the file at the same time - hence the need for a shared workbook.

I have produced a file which contains a macro to restrict the view of the file by user using very hidden worksheets (see macro 1 below) and various other macros to protect/ unprotect sheets and the workbook, restrict selection or allow selection on worksheets, etc.

Macro 1:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal IpBuffer As String, nSize As Long) As Long
Public UserName As String
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call Unprotect_Workbook_and_Sheets
Call Hide_Sheet(UserName)

End Sub

Private Sub Workbook_Open()
UserName = fNTUserName

Call UnHide_Sheet(fNTUserName)
Call Protect_Workbook_and_Sheets

End Sub

Sub UnHide_Sheet(UserName As String)

Select Case UserName

Case "xxx", "xxx", "xxx" ' Admin View
Worksheets("MAN_DR_Timesheet").Visible = True
Worksheets("MAN_DR_Submission").Visible = True
Worksheets("MAN_WK_Timesheet").Visible = True
Worksheets("MAN_WK_Submission").Visible = True
Worksheets("MID_DR_Timesheet").Visible = True
Worksheets("MID_DR_Submission").Visible = True
Worksheets("LEE_Timesheet").Visible = True
Worksheets("LEE_Submission").Visible = True
Worksheets("PUR_Timesheet").Visible = True
Worksheets("PUR_Submission").Visible = True
Worksheets("Weekly Input").Visible = True
Worksheets("Weekly Rates").Visible = True
Worksheets("Control").Visible = True
Worksheets("Lookup").Visible = True
Worksheets("Notice").Visible = False

Case "xxx", "xxx" ' MAN DR View
Worksheets("MAN_DR_Timesheet").Visible = True
Worksheets("MAN_DR_Submission").Visible = True
Worksheets("Notice").Visible = False

Case "xxx", "xxx" ' MAN WK View"
Worksheets("MAN_WK_Timesheet").Visible = True
Worksheets("MAN_WK_Submission").Visible = True
Worksheets("Notice").Visible = False

Case "xxx", "xxx", "xxx" ' MID DR View
Worksheets("MID_DR_Timesheet").Visible = True
Worksheets("MID_DR_Submission").Visible = True
Worksheets("Notice").Visible = False

Case "xxx", "xxx" ' LEE View
Worksheets("LEE_Timesheet").Visible = True
Worksheets("LEE_Submission").Visible = True
Worksheets("Notice").Visible = False

Case "xxx", "xxx" ' PUR View
Worksheets("PUR_Timesheet").Visible = True
Worksheets("PUR_Submission").Visible = True
Worksheets("Notice").Visible = False

End Select

End Sub

Sub Hide_Sheet(UserName As String)

Worksheets("Notice").Visible = True
Worksheets("MAN_DR_Timesheet").Visible = xlVeryHidden
Worksheets("MAN_DR_Submission").Visible = xlVeryHidden
Worksheets("MAN_WK_Timesheet").Visible = xlVeryHidden
Worksheets("MAN_WK_Submission").Visible = xlVeryHidden
Worksheets("MID_DR_Timesheet").Visible = xlVeryHidden
Worksheets("MID_DR_Submission").Visible = xlVeryHidden
Worksheets("LEE_Timesheet").Visible = xlVeryHidden
Worksheets("LEE_Submission").Visible = xlVeryHidden
Worksheets("PUR_Timesheet").Visible = xlVeryHidden
Worksheets("PUR_Submission").Visible = xlVeryHidden
Worksheets("Weekly Input").Visible = xlVeryHidden
Worksheets("Weekly Rates").Visible = xlVeryHidden
Worksheets("Control").Visible = xlVeryHidden
Worksheets("Lookup").Visible = xlVeryHidden

End Sub
Function fNTUserName() As String

Dim lngLen As Long, lngX As Long

Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX < 0 Then
fNTUserName = Left$(strUserName, lngLen - 1)
fNTUserName = ""
End If

End Function

Everything is working fine until I save the file as a shared workbook.

Has anyone got any ideas on this can be done?

Many thanks

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
Posts: 1,182
Default Using very hidden macros (plus other macros) on a shared file

You might want to consider a different approach where the code source
is an xla that uses a data.xls that users inut to. As an example...

My TimesheetManager.xla uses a login dialog over a background sheet in
the pay period file to display a user's sheet when opened. The save
process hides the current user sheet and redisplays the login dialog.
This simplifies managing user access to other sheets in the workbook
because by default all sheets but "Login" are hidden and protected, and
only their sheet gets displayed on successful login (username/password
match stored info).

Login info is stored on "UsrDat" in the xla. Each new timesheet gets
automatically populated with user info. New users are added via pg2 of
the login dialog if their name/info isn't in the UsrDat list.

Admin access to addin features also requires a separate login. This is
how user info is managed, and gives admin the option to set passwords,
add/remove users, and perform various timesheet processing tasks.
Successful login enables the 'Admin' menus for these features. When
admins logout, the default startup UI displays.

The addin is licensed (locked) to the login PC and the Payroll PC
machines. (My licensing methodology allows 2 seats per subscription,
but additional seats can be attached to a subscription as needed for
marginal cost)

Probably too much info but...

The addin supports both Weekly and BiWeekly pay periods. It uses an xlt
that changes every pay period (weekly), and which contain no code.
Timesheets are created from the master sheet in the xlt. This
facilitates revisons without having to tamper with the xla.

Weekly workbooks are sent to payroll. The pay period is tagged as

Today (Sep2) falls in pay period 37, which gets paid on Fri Sep9;
The workdays for this pay period are Sun Aug28 to Sat Sep03

The timesheet 'Week' field is: Aug28-Sep03 (pp37)

BiWeekly pay periods ID which week the timesheet applies to (A or B).
The timesheet 'Week' field gets populated as follows:

Today (Sep2) falls in week 'B' of pay period 19, which gets paid
on Fri Sep16;

The timesheet 'Week' field is: Aug28-Sep03 (pp19B)

All this is obtained from pay period tables within the addin. These
tables utomatically update each calendar year to match their respective
pay dates.


Free usenet access at
Classic VB Users Regroup!

This email has been checked for viruses by Avast antivirus software.

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Fwd: Running Macros in a Shared File Alex[_38_] Excel Programming 0 January 28th 09 03:20 PM
Running Macros in a Shared File [email protected] Excel Programming 2 May 5th 08 08:26 PM
Macros in shared Workbook. Seasider Excel Programming 2 February 5th 06 11:29 PM
Shared Workbook and Macros Bernardo Lozano[_2_] Excel Programming 0 February 17th 05 05:59 PM
Can we create/run macros in Shared excel file. How? Ravish Jain Excel Programming 1 September 20th 04 11:38 AM

All times are GMT +1. The time now is 08:32 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"