activate/deactivate macro depending on who's the user

in my office we have a shared drived folder that is used by different 
users,we store excel worksheets in the shared folder. Each user have to log 
in to the PC with a different ID to start using the it. 

I need a macro to automatically hide/unhide rows in the excel worksheet 
depending on who the user is. 

How should i write the macro? Please help.
0
Utf
1/24/2010 1:09:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
663 Views

Similar Articles

[PageSpeed] 0

Hi Anna

This should get you started

Sub UnHideCols()
    Dim user As String
    user = Environ("Username")
    With Sheets("Sheet1")
    ' you have to leave at least 1 column visible on a sheet
    ' so set all but column A as hidden to begin with
        Columns("B:IV").EntireColumn.Hidden = True
        Select Case user
        Case "Anna"
            Columns("B:H").EntireColumn.Hidden = False
        Case "Roger"
            Columns("I:M").EntireColumn.Hidden = False
    ' add as many other users as you wish, with their
    ' relevant columns hidden property set to false

        Case Else
        End Select
    End With
End Sub

-- 
Regards
Roger Govier

"anna" <anna@discussions.microsoft.com> wrote in message 
news:F4DC3DA5-CE33-443B-9A7B-917B124757BA@microsoft.com...
> in my office we have a shared drived folder that is used by different
> users,we store excel worksheets in the shared folder. Each user have to 
> log
> in to the PC with a different ID to start using the it.
>
> I need a macro to automatically hide/unhide rows in the excel worksheet
> depending on who the user is.
>
> How should i write the macro? Please help.
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4801 (20100124) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

__________ Information from ESET Smart Security, version of virus signature database 4801 (20100124) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
1/24/2010 3:58:40 PM
Hi Anna

Sorry, I didn't read your post carefully enough.
You said you wanted to hide Rows, not columns.


-- 
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
news:62F632ED-7D74-49F6-A810-D955A501E133@microsoft.com...
> Hi Anna
>
> This should get you started
>
> Sub UnHideCols()
>    Dim user As String
>    user = Environ("Username")
>    With Sheets("Sheet1")
>    ' you have to leave at least 1 column visible on a sheet
>    ' so set all but column A as hidden to begin with
>        Columns("B:IV").EntireColumn.Hidden = True
>        Select Case user
>        Case "Anna"
>            Columns("B:H").EntireColumn.Hidden = False
>        Case "Roger"
>            Columns("I:M").EntireColumn.Hidden = False
>    ' add as many other users as you wish, with their
>    ' relevant columns hidden property set to false
>
>        Case Else
>        End Select
>    End With
> End Sub
>
> -- 
> Regards
> Roger Govier
>
> "anna" <anna@discussions.microsoft.com> wrote in message 
> news:F4DC3DA5-CE33-443B-9A7B-917B124757BA@microsoft.com...
>> in my office we have a shared drived folder that is used by different
>> users,we store excel worksheets in the shared folder. Each user have to 
>> log
>> in to the PC with a different ID to start using the it.
>>
>> I need a macro to automatically hide/unhide rows in the excel worksheet
>> depending on who the user is.
>>
>> How should i write the macro? Please help.
>>
>> __________ Information from ESET Smart Security, version of virus 
>> signature database 4801 (20100124) __________
>>
>> The message was checked by ESET Smart Security.
>>
>> http://www.eset.com
>>
>>
>>
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4801 (20100124) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4802 (20100124) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

__________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
1/24/2010 11:07:26 PM
Hi Anna

Sorry, I misread your request. You want to hide Rows, not Columns.

Sub UnHideRows()
    Dim user As String
    user = Environ("Username")
    With Sheets("Sheet1")
    ' you have to leave at least 1 rowvisible on a sheet
    ' so set all but column A as hidden to begin with
        Rows("2:65536").EntireRow.Hidden = True
        Select Case user
        Case "Anna"
            Rows("2:30").EntireRow.Hidden = False
        Case "Roger"
            Rows("31:60").EntireRow.Hidden = False
    ' add as many other users as you wish, with their
    ' relevant rows hidden property set to false

        Case Else
        End Select
    End With
End Sub


-- 
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
news:62F632ED-7D74-49F6-A810-D955A501E133@microsoft.com...
> Hi Anna
>
> This should get you started
>
> Sub UnHideCols()
>    Dim user As String
>    user = Environ("Username")
>    With Sheets("Sheet1")
>    ' you have to leave at least 1 column visible on a sheet
>    ' so set all but column A as hidden to begin with
>        Columns("B:IV").EntireColumn.Hidden = True
>        Select Case user
>        Case "Anna"
>            Columns("B:H").EntireColumn.Hidden = False
>        Case "Roger"
>            Columns("I:M").EntireColumn.Hidden = False
>    ' add as many other users as you wish, with their
>    ' relevant columns hidden property set to false
>
>        Case Else
>        End Select
>    End With
> End Sub
>
> -- 
> Regards
> Roger Govier
>
> "anna" <anna@discussions.microsoft.com> wrote in message 
> news:F4DC3DA5-CE33-443B-9A7B-917B124757BA@microsoft.com...
>> in my office we have a shared drived folder that is used by different
>> users,we store excel worksheets in the shared folder. Each user have to 
>> log
>> in to the PC with a different ID to start using the it.
>>
>> I need a macro to automatically hide/unhide rows in the excel worksheet
>> depending on who the user is.
>>
>> How should i write the macro? Please help.
>>
>> __________ Information from ESET Smart Security, version of virus 
>> signature database 4801 (20100124) __________
>>
>> The message was checked by ESET Smart Security.
>>
>> http://www.eset.com
>>
>>
>>
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4801 (20100124) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4802 (20100124) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

__________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
1/24/2010 11:11:01 PM
Reply:

Similar Artilces:

Tracking Multiple User Activities in Contacts
We have Outlook 2003 and Exchange 2003. In our office there are three physical therapists that each have a calendar. We have it setup so that the receptionist and everyone can view each others calendars and make and change appointments. It's great to be able to see the three calendars side by side when making appointments for patients. What we haven't been able to do so far is: Have one set of shared contacts that we can look at and assign (link) appointments too. We would like to see upcoming appointments on the activities tab. Is there any way possible to have every use...

Remove users from FRx
Hi, Does FRx have a screen or file where I can find who is logged in? Also is there a way to remove people from FRx in a similar fashion as deleting the user activity in Great Plains? Thanks, Peter There is no way to remove somebody through the interface. One way you can remove them is to go to the server where you are sharing your SYSDATA folder and find the open sessions for that share and disconnect who you want. If you are using citrix just remove them from citrix though the Management Console. "PeterH" wrote: > Hi, > > Does FRx have a screen or file where...

email spoofed by local user (April Fools)
Recently (on April fools day) one of our local users spoofed an email to look like the CTO created it and sent it to the rest of the organization. Very funny (unless you are the Exchange administrator). We run Exchange 2003 and I need to fix this problem so it doesn't happen again. Right now if I telnet the mail server and issue the right commands (mail from:, rcpt to:, etc.) I can send an email with any non local address in the "mail from:" field to any user at my domain without authentication, which is how it must be since you can't expect users from other organizati...

How to write a simple copy-paste macro?
I've used 123 for over 20 years - simple, easy to learn and use, but my XP OS doesn't handle it well, so I'm trying to move to Excel. But in Excel I can't even write a simple macro such as: Copy from a cell, move down one space, Paste. It will do this once, but then not continue. If I run it from another cell - just restarts from the original cell. Without further comment, I learned this macro in 2 minutes on 123. I've been working with Excel for a week now, and I'd appreciate some help. To recap, I simply want to be able to choose a cell having data, run a C...

macro help #17
Hi All, 3*2 3*3 3*2 3*4 3*4 3*4 3*2 3*2 3*5 3*5 3*4 3*4 3*4 3*4 3*5 3*5 3*5 3*5 3*4 3*4 3*4 3*4 3*4 3*4 3*2 3*2 3*3 3*3 I have following kind of data i want to convert the above column into following , how can i do that =3*2 =3*3 =3*2 =3*4 =3*4 =3*4 =3*2 =3*2 =3*5 =3*5 =3*4 =3*4 =3*4 =3*4 =3*5 =3*5 =3*5 =3*5 =3*4 =3*4 =3*4 =3*4 =3*4 =3*4 =3*2 =3*2 =3*3 =3*3 Can i do it using the help of macro ? Regards, Raan Sub multiply() For Each cell In Selection cell.Value = "=" & cell.Value Next cell End Sub Gord Dibben MS Excel MVP On Sat, 17 Nov 2007 08:25:09 -0800 (PST), ...

Macro problem for timesheet
Currently, I am doing a timesheet. The timesheet are given every month. For December, a file December.xls with sheet name - December as well is given to them. Then, the staff are required to rename the file same as their staff number. So, for staff no. P101 and in December, the sheet name is December where else the file name is P101.xls. And, so on. Then, I have to manually renamed all the sheet name same as the staff's' numbers. This for the purpose where the formula that need to refer to data entered by the particular staff, I can just easily refer by the sheet name. Th...

set macro security to low programmatically
Dear all, Is it possible to set macro security to low programmatically?Thanks.Chlaris On Tue, 29 Dec 2009 11:32:10 +0700, "Chlaris" <chlarrissa_nospam@yahoo.com> wrote: There are a few registry keys you can set BEFORE you run your app, for example by your setup program. See below for details. IMHO it would be much better to digitally sign your app, so this is not needed. Root: Local Machine Key: Local Machine Software\Microsoft\Office\11.0\Access\Security Name: level Value #00000001 Root: Local Machine Key: Local Machine SOFTWARE\Microsoft\Jet\4.0...

Receiving email addressed to non-existent users in my domain
I'm using Exchange 2000 with GFI MailEssentials spam filter. I'm trying to figure out how email addressed to non-existent users in my domain wind up coming to me. Although I administer my Exchange installation I have not designated an administrator within Exchange Server. If I send an email from my personal Yahoo account to one of the accounts I've received email for it gets rejected as undeliverable by my Exchange server. However, when spammers sends to that address it gets through to me and I don't know how. GFI identifies it as spam and moves it to my spam folder but I recei...

User account locked out
Bonjour! One of our user receive an error message "This User account has been lock out. Contact your Sys Admin." when trying to login into GP yesterday and she is still Locked out this morning. Joel :) Joe, Try the following 1. Go to Tools=>Utilities=>System=>User Activity and then Delete the User who is locked out. If this doesn't work out 2. Go to SQL, Run the script below in DYNAMICS Database DELETE FROM SY00800 WHERE USERID = 'XXX' ---Replace XXX with the User ID Thanks Janakiram M.P. MCP-GP "Joe" wrote: > Bonjour! One of our user re...

User Guide
I have been trying to open the User guide for 2004 and it doesn't. So I follow the steps to preform to fix this and it still will not open. What am I doing wrong You can access the user's guide on the web here: http://www.microsoft.com/money/support/manual/ Tina wrote: > I have been trying to open the User guide for 2004 and it > doesn't. So I follow the steps to preform to fix this and > it still will not open. What am I doing wrong Are we talking on-line help? Try flushing your browser cache. "Tina" <anonymous@discussions.microsoft.com> wrote in ...

Need to change user acct. from mail enabled to mailbox enabled
We currently have a consultant working for us whose AD user account is mail enabled. All email is forwarded to his personal email address. We now want him to have a mailbox on the Exchange server, but I don't know how to transition his account from mail enabled to mailbox enabled. I could remove the exchange attributes and start over, but I get a dire warning when I start to go down that road. I've tried adding a new smtp address for him and making it the primary, but that doesn't create a corresponding mailbox on the server. Any suggestions? Thanks in advance ~ Jim ...

Director doesn't want a user to receive/send email
I have a user is using the Outlook 2007 client, the director doesn't want this user to be able to send/receive email from inside and/or outside addresses she only wants this person to access calendars from w/in their department and schedule appointments. How can I disable this? I tried to delete the smtp address (won't allow it) and I tried to remove them from the address book (didn't seem to matter). To disable receiving from anyone: ADUC | recipient properties | Exchange General | Delivery Restrictions | Accept Messages -> select "Only From" | click "...

Track Active Directory Connector Changes
Does anyone know how to monitor or track changes in an Exchange 5.5 site made by the Active Directory Connector? In a nutshell, we want to monitor any ADC changes in our Exchange 5.5 environment. Thanks. ...

A macro question
Hello, I have a macro that when prompted opens up many different files that are located in the same folder. Each of these files that I am opening are files that have been previously summarized through the use of a macro. Example: I wll have 40 to 60 files that I save in the same folder on a daily basis. At the end of the day I use a template and a macro that will open all of the daily files and summarize them into one neat sheet for me. When this macro is executed all of the 40 to 60 daily files are opened to give me the total of my daily summary sheet. I save each of these daily summari...

Active mode cache and active autodetection
Hello, I have to migrate workstations from Office XP to Office 2003 with using existinng users profils. I would like to activate Outlook cache mode, and set the detection of connexion state to "automatic". Bot of these option can be set thank to GPO even if based on Outlook.Adm template. For the cache mode I saw tha in step 17 and 18 of custom installation wizard I could enable it with specifying to use existing user profil. But when I deploy my package, the test user do not have their cache mode enable. For "auto detect the connexion state" option i...

activity list freezes
In the past few days our users have been reporting that their activity list does not load. Everything else seems to be fine, and when I look at the SQL Server activity monitor, I see a suspended process with open transactions that involve an update or insert on the activity table. If I manually kill this process, the activity list loads fine. I ran the SQL profiler and was able to link the problem to one specific user who was trying to assign an activity to another user. She would get an error, and then everyone's activity lists would hang. She said she's not doing anything dif...

Enable/Disable Macros question
Hi I have a workbook that has a few macros in it which need to be enabled. How can i close the workbook if someone clicks 'disable macros'.? I would like to place the 'check' in the workbook open module if possible. Thanks Paul On Fri, 13 Feb 2004 18:19:05 -0000, "Paul Watkins" <paul.watkins4@ntlworld.com> wrote: >Hi > >I have a workbook that has a few macros in it which need to be enabled. >How can i close the workbook if someone clicks 'disable macros'.? > >I would like to place the 'check' in the workbook open modu...

Save sheet copy with cell value name using a macro.
Good afternoon All, I need a VB statement to save a copy of the sheet i'm using (same were the macro is executed) with a cell value (EG cell "B2") of the workbook I'm using into a predefined path (EG "C:\"). If anybody can help me with this I'll be very glad. Leo. You want to save a sheet as a workbook with a name of the value in Range("B2") of the sheet? Sub Make_New_Book() Dim w As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False ActiveSheet.Copy ActiveWorkbook.SaveAs Filename:="C:\...

Would like better understanding of how this macro works.
I found most of the macro online and made some changes, but I'm not sure of all of the functions being done. The macro lets you select a group of graphic files and then inserts them into a Word table with one graphic per row. I would appreciate comments on what the different parts of the macro do. Sub AddPix() Dim fd As FileDialog ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1, NumColumns:= _ 2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _ wdAutoFitContent With Selection.Tables(1) .Columns.Pre...

Outlook Appointments off by 1 Hour on some Users Outlook Calendar
I have some users that have SOME appointments (both recurring and single instance) that are off by 1 hour. I have tried the Outlook Time Zone Move program on 3 separate computers (all runing Win XP Pro/Outlook 2007) and the program "finds no appointments that need fixing"...but there are clearly one or more that need fixed if I manually look in their calendar. The weird thing is that when I run the tool on my Outlook (Windows 7 Ult/Outlook 2007) it still shows no appointments need fixing but it gives me a "details" button to show my appointments where I can ...

Additional Customer Lookup Field On Activity Forms
Hello, I am wondering if it is possible to add a second customer look up on a activity form? Thanks in Advance "swilcox" <swilcox@discussions.microsoft.com> wrote in message news:1C9AA9B6-2E5C-4986-B499-EA64A11F54E1@microsoft.com... > Hello, I am wondering if it is possible to add a second customer look up > on a > activity form? Thanks in Advance Not in CRM 3.0, but if you are working on CRM 4.0 then you use http://www.stunnware.com/crm2/topic.aspx?id=JS34 as a starting point. CRM itself does not support customer fields except the predefined ones. Michael ...

Excel 2007 Countifs macro with multiple criteria (OR)
Been working on this reporting macro for excel 2007. Cant figure out how to make countifs work for many possible values in one cell. I have report with multiple colums. With no problem I can check if call has been on hold for less than 26 seconds etc. But in the same countifs sentence I should check also if colum H cell has one of correct names from 50 possible names. There is something like 50 names and about 30 names should be counted and the other 20 not. So I think I need somekind of a OR sentence inside criteria (also saw one possible solution that there would be possib...

Array activation question
Morning all. I'm making a user form to simplify the input of data for a complicated worksheet function. One of the things I was told about this function is that it requires a specific key combination-- ctrl+shift+enter, with the cell being active (the cursor needs to be active within the cell field, or the formula bar). Apparently this places two {} on the outsides of the equation, to do something to it that makes it work. And it's not enough to just place the {} on the equation manually. It specifically requires the keystroke combination mentioned above. My questio...

How to get user name which is active in windows
I am working a software which can set specific conf. for each user, so my program needs to know which user is active now, which API or MFC class can do it? -- Nothing impossible, Nothing sure GetUserName/GetUserNameEx --- Ajay I do it like this: // This function is useful for retrieving the current user name BOOL GetUser(CString &csUser) { TCHAR szUserName[80]; DWORD dwResult, cchBuff = 80; // Call the WNetGetUser function. dwResult = WNetGetUser(NULL, (LPTSTR) szUserName, &cchBuff); if(dwResult != NO_ERROR) return false; csUser = szUserName; ...

Frozen Activities
In our production environment there are four avtivities that are frozen. You can open them but nothing else works. When you try to delete it times out. Any thoughts? try opening up crm directly on the crm server and try the same operation. also check you crm event logs. "Peter B" <pbertell@ibisinc.com> wrote in message news:092d01c39188$95fe5a00$a001280a@phx.gbl... > In our production environment there are four avtivities > that are frozen. You can open them but nothing else > works. When you try to delete it times out. Any > thoughts? We have a (i b...