Using UserName in Select Case

**jomili** · 05-21-2010, 04:11 PM

I have a macro, code below, which fires when the workbook is opened. It checks to see if the username is written in column A of Sheet2; if it is, it writes the date beside it. If it isn't, it writes the username in the next open cell in column A, puts the date beside it, saves itself, then displays a timed messag of my choosing.

Right now I can put the UserName (StrUser) in my message, but I want to personalize it more by putting the actual name (User) in the message, and I'm trying it with a Select Case routine, but it's not working. I always plugs in "I don't know who you are", though I've verified my StrUser name is correct. I'd appreciate it if anyone can point out my problem in the code below. BTW, the "Sheet1.Select" statements are because I want my users to only be looking at sheet1.

Thanks.

Please Login or Register  to view this content.

**davegugg** · 05-21-2010, 04:43 PM

I think it should be:

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

I believe without that extra period in front of cells, it refers to the active sheet, which would be sheet 1 in your case.

If that doesn't do it, try doing a

Please Login or Register  to view this content.

right before the select case statement and see what the immediate window says is there.

Just out of curiosity, is there a reason you are using .text instead of .value?

**snb** · 05-21-2010, 05:33 PM

somewhat simpler:

Please Login or Register  to view this content.

**jomili** · 05-24-2010, 08:31 AM

Dave,
You were right on the dot! I can't believe one period made the difference, but it sure did. I would never have found that. Thanks so much!

SNB,

I like your simpler code, but have some questions:
I want to exchange the Environ("Username") for their actual name, and don't see a routine in your code to do that. For instance, in using Dave's solution, the message prints up as "Hello John" instead of "Hello LUPOREJ".

**jomili** · 05-24-2010, 10:59 AM

SNB,

I'm having trouble implementing your code. I'm getting a Compile Error at this line

Please Login or Register  to view this content.

It's highlighting the "(.Rows." piece. Here's the full macro I tried to make using your code:

Please Login or Register  to view this content.

Also, anybody; the UserName may come in in all caps, or all lowercase, or a mixture. How do I configure my Select Case to recognize the UserName regardless of the case?

**Leith Ross** · 05-24-2010, 01:21 PM

Hello jomili,

To make the Case case insensitive, you can first convert your string to all upper or lower case letters.

I don't see a With statement before the Select Case. The period in front of Cells will cause you problems. You should remove it to refer to the ActiveSheet.

Please Login or Register  to view this content.

**jomili** · 05-24-2010, 02:47 PM

Leith,

Thanks for chiming in. There IS a With statement before the Select Case; does it need to be closer?

Regarding the period before "cells", I want my users looking at sheet1 (the activesheet) while the action occurs on sheet2. The period was suggested by Davegugg earlier.

I put in the Ucase the way you indicated (see code below), and it doesn't appear to be working. My name comes across as LUPOREJ, but the macro doesn't recognize that as equivalent to Luporej.

Please Login or Register  to view this content.

**Leith Ross** · 05-24-2010, 03:13 PM

Hello Jomili,

The user names following the Case statements must in Upper Case ro the comparisons fails. I have made the correction in the code below. This run correctly.

Please Login or Register  to view this content.

**snb** · 05-24-2010, 03:41 PM

My code was meant to be a replacement for your code. Further testing is redundant:

Please Login or Register  to view this content.

I made 2 changes. ( you already noticed)
If you work with this kind of code you must be able to find application.username yourself...

**jomili** · 05-24-2010, 04:12 PM

Leith,
It's working, but now something else isn't. it's supposed to exit the sub if it finds my username on Sheet2, but it's acting as if it's not there. Any ideas?

Please Login or Register  to view this content.

SNB,
I changed your code slightly. Instead of

Please Login or Register  to view this content.

I used

Please Login or Register  to view this content.

as this is the value I'm capturing on Sheet2. However, when it shows the message, It's showing both my UserName AND "I don't know who you are", so I think somethign might be wrong with the below code.

Please Login or Register  to view this content.

**Leith Ross** · 05-24-2010, 04:26 PM

Hello jomili,

Please post the code, with the corrections, as it appears now. I am having trouble following you.

**jomili** · 05-24-2010, 04:49 PM

Sorry Leith. That's what I get for trying to carry on two conversations at once. Here's the full code:

Please Login or Register  to view this content.

It's supposed to be 1) showing Sheet1, 2) looking to see if my username exists on sheet2, and if so, 3) logging the date and exiting the sub. OR, if my UserName doesn't exist, log it, write the date, and show the message. However, every time I open my spreadsheet I get the message.

**Leith Ross** · 05-24-2010, 05:33 PM

Hello jomili,

I first ran a workbook with the code as it is now. My user name was logged onto "Sheet2", as expected since my user name was not in the Select Case statements. After adding my user name to the Select Case statements, saving the workbook, and reopening it, it worked correctly showing my name in the message box. Perhaps you should post your workbook for review because the problem isn't with the Workbook_Open event macro code as it is now.

**jomili** · 05-25-2010, 08:14 AM

Okay, it's attached.

The problem is not that it's not recording my username and date, the problem is that it's recording my name each time, as if it weren't there before, and displaying the message.. It should look for the username, if it finds a match, put the data beside it and exit the sub. So, it shouldn't play the message the second time someone opens the workbook, only the first.

**Kyle123** · 05-25-2010, 08:33 AM

I put a sheet2. in the below and it works fine now

Please Login or Register  to view this content.

**jomili** · 05-25-2010, 08:37 AM

When I use your code I get a compile error, Variable not Defined, highlighting the "Sheet2" I just added. I'm using Excel 2003.

**Kyle123** · 05-25-2010, 08:44 AM

Thats odd

, have you changed the Name of Sheet2 in the properties window? If so try the below

Please Login or Register  to view this content.

I think your problem is that you have selected sheet 1, the code is then looping through column A in sheet 1 rather than 2.

**jomili** · 05-25-2010, 09:27 AM

Sorry about the oddness. Yes, turns out Sheet2 was actually named Sheet3(Sheet2). I renamed Sheet3 to Sheet2 and now that part appears to be working, so I think it's all working right now. Thanks so much for sticking with me and helping me get through this one. I'll mark it solved now.

Using UserName in Select Case

LinkBack

Thread Tools

Rate This Thread

Display

Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Re: Using UserName in Select Case

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions