Target.Offset(0, 36).Value Help

In the below, I use this to force the persons USERNAME to populate 36 rows to 
the right in a column I hide.  This shows me who was in a spreadsheet and 
updating ONE specific column... Column AD.  My question is this... Can I 
poulate a named range with their USERNAME. I want to insert a named range, 
then force the USERNAME there. (INSERT>NAME>DEFINE>)



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const myRange As String = "ad7:AD90"
'Const myrange As String = "A3,A6,A8,A11,A16"
On Error GoTo stoppit
Application.EnableEvents = False
If Intersect(Target, Me.Range(myRange)).Value <> "" Then
Target.Offset(0, 36).Value = Environ("USERNAME")
Else
If Intersect(Target, Me.Range(myRange)).Value = "" Then
Target.Offset(0, 36).Value = ""
End If
End If
stoppit:
Application.EnableEvents = True
End Sub

0
Utf
4/1/2010 4:56:10 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
846 Views

Similar Articles

[PageSpeed] 49

Matt;687987 Wrote: 
> 
In the below, I use this to force the persons USERNAME to populate 36
rows to
> the right in a column I hide.  This shows me who was in a spreadsheet
and
> updating ONE specific column... Column AD.  My question is this... Can
I
> poulate a named range with their USERNAME. I want to insert a named
range,
> then force the USERNAME there. (INSERT>NAME>DEFINE>)
> 
> 
> 
> 
> 

VBA Code:
--------------------
  > 

  > 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  > Const myRange As String = "ad7:AD90"
  > 'Const myrange As String = "A3,A6,A8,A11,A16"
  > On Error GoTo stoppit
  > Application.EnableEvents = False
  > If Intersect(Target, Me.Range(myRange)).Value <> "" Then
  > Target.Offset(0, 36).Value = Environ("USERNAME")
  > Else
  > If Intersect(Target, Me.Range(myRange)).Value = "" Then
  > Target.Offset(0, 36).Value = ""
  > End If
  > End If
  > stoppit:
  > Application.EnableEvents = True
  > End Sub
--------------------
> 

> 




Let's say you already have a defined name 'blah' defined in the
workbook.
It can be referred to by using:


VBA Code:
--------------------
  

  
range("blah")
--------------------



so


VBA Code:
--------------------
  

  
range("blah").value = environ("username")
--------------------



should work?


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192291

http://www.thecodecage.com/forumz

0
p45cal
4/1/2010 5:13:13 PM
It kinda worked...the problem is that the range I named was specifically.... 
Z7:Z91.  the USERNAME populated all 85 cells from Z7:Z91.  THe intent is to 
only populate the same row, hence the Target.Offset(0, 36).

I am curious to see if I can expand the Const myRange As String = "ad7:AD90"
 to 
Const myRange As String = "E7:AD90"

This will show that if anyone changed a field from E7 to AD90, I want column 
Z and then that specific row.

Make sense???
Thanks Matt 


"p45cal" wrote:

> 
> Matt;687987 Wrote: 
> > 
> In the below, I use this to force the persons USERNAME to populate 36
> rows to
> > the right in a column I hide.  This shows me who was in a spreadsheet
> and
> > updating ONE specific column... Column AD.  My question is this... Can
> I
> > poulate a named range with their USERNAME. I want to insert a named
> range,
> > then force the USERNAME there. (INSERT>NAME>DEFINE>)
> > 
> > 
> > 
> > 
> 
> 

> VBA Code:
> --------------------
>   > 
> 
  > 
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>   > Const myRange As String = "ad7:AD90"
>   > 'Const myrange As String = "A3,A6,A8,A11,A16"
>   > On Error GoTo stoppit
>   > Application.EnableEvents = False
>   > If Intersect(Target, Me.Range(myRange)).Value <> "" Then
>   > Target.Offset(0, 36).Value = Environ("USERNAME")
>   > Else
>   > If Intersect(Target, Me.Range(myRange)).Value = "" Then
>   > Target.Offset(0, 36).Value = ""
>   > End If
>   > End If
>   > stoppit:
>   > Application.EnableEvents = True
>   > End Sub

> --------------------
> > 
> 
> 
> 
> 
> 
> 
> Let's say you already have a defined name 'blah' defined in the
> workbook.
> It can be referred to by using:
> 
> 

> VBA Code:
> --------------------
>   
> 
  
> range("blah")

> --------------------
> 
> 
> 
> so
> 
> 

> VBA Code:
> --------------------
>   
> 
  
> range("blah").value = environ("username")

> --------------------
> 
> 
> 
> should work?
> 
> 
> -- 
> p45cal
> 
> *p45cal*
> ------------------------------------------------------------------------
> p45cal's Profile: 558
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192291
> 
> http://www.thecodecage.com/forumz
> 
> .
> 
0
Utf
4/1/2010 6:32:01 PM
in a simple way:


VBA Code:
--------------------
  

  
Private Sub Worksheet_Change1(ByVal Target As Range)
  Set x = Intersect(Range("E7:AD90"), Target)
  If Not x Is Nothing Then
  Cells(Target.Row, "AF").Value = Environ("USERNAME")
  End If
  End Sub
--------------------



will put the username in column AF (I didn't use Z because that's
included in the range E7:AD90 (though you could, but reintroduce the
Application.EnableEvents lines and the error handler)).

This won't handle where someone may have selected a range covering
several rows/columns and deleted or ctrl-entered a value.
This, however does handle that, and handles if the user selects a
non-contiguous range too:


VBA Code:
--------------------
  

  
Private Sub Worksheet_Change(ByVal Target As Range)
  Set x = Intersect(Range("E7:AD90"), Target)
  If Not x Is Nothing Then
  For Each ar In x.Areas 'to handle non-contiguous ranges
  For Each rw In ar.Rows
  Cells(rw.Row, "AF").Value = Environ("USERNAME")
  Next rw
  Next ar
  End If
  End Sub
  
--------------------



It doesn't start trying to discern if someone deleted a value or not
and erase the username if that's the case, because they may have addedd
values in some columns and deleted values in others.. all showing the
name in the same column. So I've just got it to add the name for *any*
change: value added/changed or deleted.


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192291

http://www.thecodecage.com/forumz

0
p45cal
4/1/2010 7:33:55 PM
Awesome!  THanks

"p45cal" wrote:

> 
> in a simple way:
> 
> 

> VBA Code:
> --------------------
>   
> 
  
> Private Sub Worksheet_Change1(ByVal Target As Range)
>   Set x = Intersect(Range("E7:AD90"), Target)
>   If Not x Is Nothing Then
>   Cells(Target.Row, "AF").Value = Environ("USERNAME")
>   End If
>   End Sub

> --------------------
> 
> 
> 
> will put the username in column AF (I didn't use Z because that's
> included in the range E7:AD90 (though you could, but reintroduce the
> Application.EnableEvents lines and the error handler)).
> 
> This won't handle where someone may have selected a range covering
> several rows/columns and deleted or ctrl-entered a value.
> This, however does handle that, and handles if the user selects a
> non-contiguous range too:
> 
> 

> VBA Code:
> --------------------
>   
> 
  
> Private Sub Worksheet_Change(ByVal Target As Range)
>   Set x = Intersect(Range("E7:AD90"), Target)
>   If Not x Is Nothing Then
>   For Each ar In x.Areas 'to handle non-contiguous ranges
>   For Each rw In ar.Rows
>   Cells(rw.Row, "AF").Value = Environ("USERNAME")
>   Next rw
>   Next ar
>   End If
>   End Sub
>   

> --------------------
> 
> 
> 
> It doesn't start trying to discern if someone deleted a value or not
> and erase the username if that's the case, because they may have addedd
> values in some columns and deleted values in others.. all showing the
> name in the same column. So I've just got it to add the name for *any*
> change: value added/changed or deleted.
> 
> 
> -- 
> p45cal
> 
> *p45cal*
> ------------------------------------------------------------------------
> p45cal's Profile: 558
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192291
> 
> http://www.thecodecage.com/forumz
> 
> .
> 
0
Utf
4/1/2010 9:06:05 PM
Reply:

Similar Artilces:

combo box in subform
I have a form called dailylogs based on a table called tbldailylogs. The primary key of this table is the field tdate. I have a subform in a container called [team subform]. This is based on the table tblcrews, which contains the crew id as the primary key, and tdate as a foreign key. The parent and child fields in the subform container are tdate. I have a combo box in the subform, based on a query SELECT TDATE, CREWID FROM TBLCREWS WHERE TDATE = ME.PARENT!TDATE. I also have a button to create a new crew. i have several problems - the first is that when the form is opened, it ask...

POS v.2.0 Time Clock display
Is there a file in the File Center that would permit us to implement an aggregate method so our employees can see the total hours worked when they view the Time Clock function? I'm not aware of any way to do this beyond running a report in Manager View, but it's a great suggestion. You should enter this into the suggestion database through CustomerSource. There isn't a selection for POS 2.0, so choose RMS when submitting. https://mbs.microsoft.com/customersource/ "M Kalmus" <MKalmus@discussions.microsoft.com> wrote in message news:25523729-D448-4885-AF...

Can MFC 7.0 be Integrated with VC++ 6.0?
Hey, There are some new features in MFC 7.0, such as the CDhtmlDialog for example, that I would like to use in my applications. However, vs.net is hardly a fun IDE for MFC - I haven't even been able to figure out how to (nay, IF) you can add message handlers to dialog buttons in 7.0, so I want to continue to play happily in VC++ 6.0. Any way to bring the improvements in MFC down to 6.0 or any ideas to otherwise solve this dilemna? Thanks, Dave ...

0.3128242 Look In Here Now ... 0.1098789
0.7187148 Look In Here Now ... 0.8922071 0.3128242 Now Visit http://www.clicklinknow.com/ss/ 0.1098789 ...

Lookup
Hi i have 3 fields called Site(lookup), Board(lookup) and Location(text). there is a field name which i want to populate automatically with on change event = site + location + board below is the code i have var n var lookupItem = new Array; lookupItem = crmForm.all.parentcustomerid.DataValue; var l = crmForm.all.new_location.DataValue; var boardlookup = new Array; boardlookup = crmForm.all.new_boardid.DataValue if (lookupItem[0] != null) { n = (lookupItem[0].name)+" "+ l +" "+(boardlookup[0].name) ; crmForm.all.new_name.DataValue = n; } else { n = ( l +" &qu...

4.0 Mail Templates
When I create a Word Template and save it (in Word xml format), then upload to CRM Templates, then execute a Word Merge: everything works great EXCEPT the fields need to be re-matched. This wastes a lot of time which negates the whole purpose of a template. Please advise... Thanks, Suzy This is the default behavior as I know about mail templates. -- Regards, Imran MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "Suzy" wrote: > When I create a Word Template and save it (in Word xml f...

Lookup/Find help
Windows XP Professional Office 2000 Hypothetical, but hopefully you'll get the gist of it: I have two worksheets. On worksheet #1, I have two columns. First column is a list of entire workgroup by name and 2nd column is the hours worked. On the second worksheet I simply have an list of names that is a subgroup of those on the first page. These indicate a target group. EXAMPLE Worksheet #1 Sam 35 Joe 37 Mary 20 Beth 41 Ted 38 Worksheet #2 Joe Beth Now, on the first worksheet, I want to add a third column for summing only the target workgroup. Basically, I need a function...

Outlook 2003 Target Path
We have XP Pro with Outlook 2003. I need to know the Target Path for Outlook but at the present this is greyed out and can't be altered. How do I find this?? Are you asking the file path for the Outlook.exe file? Usually it's at C:\Program Files\Microsoft Office\OFFICE11 If that's not what you're asking, please rephrase your question. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:FD896CAC-6A19-4183-AE5C-CCBF34403336@microsoft.com, Gordon wro...

SharePoint 3.0
I need to basically start over with SharePoint and SQL Express. When run the SharePoint configuation wizard, it looks for an old instace on SQL. I want to start fron scratch and do a basic install. How can I do that? Mike ...

URGENT HELP
This morning when I try to open money I get the message "We're sorry, but Microsoft Money has experienced an internal error and will have to restart." I don't even get to the point where it opens the data file when I get this message. I have tried rebooting. Also I have changed nothing on my PC since last night when it was working . Any ideas? In microsoft.public.money, Fiddle wrote: >This morning when I try to open money I get the message >"We're sorry, but Microsoft Money has experienced an internal error and will >have to restart." >I do...

Conditional Formating (how to use Offset() in cell reference)
Using XL 2003 & 97 How do enter into VBA code; the cell Offset R-12,C into the Contitional Formatting below? (I want the "not equal to" referrence a cell 12 rows above in the same column) Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=$J$6397" TIA Dennis This might help you get to the next step: Option Explicit Sub testme01() With Selection If .Row < 13 Then 'too high in the worksheet Else 'remove any existing format first????? .FormatConditions.Delete .FormatConditions.Add ...

RMS Store Operations V2.0.0110 integration with QuickBooks Premier
Hi, I am trying to setup RMS V2 to work with QuickBooks Premier: Retail Edition 2007. When attempting to import data in RMS from QB I'm getting two pop-ups: Pop-up window #1: "Could not retrieve the company from QB. Error Details: Blank response received from QB" Pop-up window #2 (shows up after I click OK on window #1): "Connection successful - You've connected to QuickBooks and retrieved the company info" The QBImport.log file also says "Blank response received from QB" and nothing gets imported. Thank you for taking the time to read this post. An...

msmoney 5.0
Hi, I did read earlier postings and do realise you're all gonna dislike this question ;-) About 6 years ago I bought a PC .. it was pre-installed with msmoney 5.0 A few weeks ago I bought a new PC ... I managed to relocate all needed programs and files ... except msmoney 5.0 !!!!! I already found out .. it's not possible to relocate this program ... but because my husband is dyslectic, and capable to work with this prog. I'm having a urge to retain ( and being capable to proceed) msmoney 5.0 Is there anyone who can tell me how to get it ?? With lots of excuses Yas ;-) ...

why it not writing to target files?
I have modified a code from this NG to help me copy my nick into a number of *.xls files in a spesific DIR. But when activated it only copies my nick to the file where the macro resides and not on the "target" files. I know I'm doing something wrong here with this "active document" part, but can't figure what the correct syntax should be. Any help is appreciated... -------------------------------- Sub Insert() Dim mesaj As String Dim FilesArray() As String, FileCounter As Integer Dim FName As String, LoopCounter As Integer FName = Dir("c:\my doc...

HTML help #3
Hello, We are trying to compile our VC6.0 application with VS .Net 2003 (unmanaged code). The help file gets created OK, but we receive a "Project Error" when the task finishes (we did not receive any error messages with VC6.0). Unfortunately, this failure causes VS to stop, and the application never gets compiled/linked. How can we determine what the failure message is? Or, how can we bypass the failure, so that VS continues and builds the application? TIA, -Jacques Deleting intermediate files and output files for project 'onmconsole', configuration 'Release|Win32&...

Word doc will not delete from print Q (Word 2000) help please
I can not delete a word doc from the print Q. (It is word 2000) It has been there for 2 days and states deleting...P but will not delete and printer will not operate, can anyone please advise how to fix. Thank you. Sorry, the print queue is not handled by Office but by the operating system. Try turning off the printer and the computer and restarting both. Diane wrote: > I can not delete a word doc from the print Q. (It is word 2000) It has been > there for 2 days and states deleting...P but will not delete and printer will > not operate, can anyone please advise ...

Import Data from ACT 6.0
Hi, I would like to import my data from ACT 6.0 into CRM 3.0., Does anybody have step by step instructions in how to do this? Thanks there are multiple options 1 - use the Microsoft data migraiton framework 2 - buy and use scribe migrate 3 - write your own code 4 - buy a 3rd party utility which transfers act to an easier database then extract from that sorry there is no easy quick way to do this ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "Behzad1m" <Behzad1m@discussions.microsoft.com> wrote in message news:7C19676E-...

Convert Works 6.0 data base to Excel?
How can I convert my data bases in Works 6.0 to Excel?? Generally you would normally open the file in WORKS and then save it down as a file that Excel can read. .xlr is a native Excel format, or .txt, .csv, dbaseIV are all formats it can read. You can also find a converter here if you don't have WORKS. http://www.rl-software.com/indexjs.htm?/converter/wkscnvxls_e.htm Text from the website above:- Microsoft does not provide an import Excel filter for Works-WKS files later than version 2. (for Works-WPD and Word they do). There is no standard way to convert a lot of Works-WKS files in...

RMS 2.0 Item Price changing to 0.00 when invoicing after installing SP1
I just upgraded my shop to SP1 and now some Workorders are having the item price changing to $0.00 after you start tendering the order. If you cancel out of the tender and change the price, give an override reason code and then tender again it works OK. This is not happening for all Workorders. Has anyone seen this? Any suggestions? Thanks, TomT I have this problem also since installing SP1. This also happens if the WO is changed by adding a line item or changing the shipping address. Also, the line item prices of any RTD items have changed showing that they were priced at a d...

HELP! Lookup, countif or if statement
Hello I have a "raw data" sheet of 1500 rows and 30 columns.... in columns J, K, L, M, T, W are names of people. In the "Lookup" sheet I have a list of leavers (I have defined the list). In the last column of the "Raw Data" sheet I need to bring back the Name/s of the leaver/s if mentioned in columns J, K, L, M, T, W. I know I need to use a nested formula just which ones? You help is greatly appreciated!! Suppose your list of leavers names is in column A of Sheet2 in the same workbook. You can have a formula like this to check if the name in J2...

CRM 3.0 Information (.NET 1.1 or .NET 2.0)?
I want to know whether Microsoft already announced which .NET version will be supported by final release CRM 3.0, .NET 1.1 or .NET 2.0? The beta version still in .NET 1.1. And also what about the SQL Server, support SQL 2005 also? As of now, the RTM version of Microsoft CRM 3 will run on .NET 1.1 SP1 and SQL 2000 SP4 (required). I know they are testing it with .NET 2.0 and SQL 2005, but I do not know if there is any official word on when they will be supported. Since both updates are "the future", it is a safe bet that Microsoft CRM 3.0 will support these new platform compo...

Urgent Help needed!!!!
Hi, I have a problem with this: I have a worksheet named Sales historical data: Column A: Dates Column B: Phone Description Column C: Sales quantity I need to get the total sales quantity for 5 days. On the left hand side, i want to create a calender in which i can choose the date. then on the right hand side, i want to create another calender in which i can choose the date again. So when i choose a date in both calender like for example, 17 Feb 2010 to 21 Feb 2010, the TOTAL sales quantity for all the 5 days will appear below. Can anyone assist me? If cannot, is...

Highlighting Cells automaticall? HELP
For some reason, when I open excel and click on my mouse all the cells automatically become shaded. I can't stop this. Does anyone know how to? According to MSFT help, I should make sure that EXT doesn't appear in my status window. It doesn't and I'm still having this problem.... Hi Stern, Since Extended Selection is apparently not your problem take a look at http://www.mvps.org/dmcritchie/excel/ghosting.txt For more information on EXT , ALT and other indicators and use of the Status Bar, but you've already eliminated this as a problem. http://www.mvps.org/dmc...

Numbers send to Excel in 0.0% format change to 0.00%
My program sends data from MSAccess to Excel. Numbers are formatted from Access as percentages with one decimal point. ex. in MSAccess number is 8.5% but when send to Excel number becomes 8.50%. Is there any reason for this ? Can it be avoided ? Is time consuming to format them again in Excel. Excel and Access have a few inconsistencies like this and I don't have a solid answer. I would check your 'percent' style in Excel under Format>Style... It may not help but is one idea. You could set a macro which runs after the data is refreshed which sets all the formats. Post back ...

Cannot upgrade to 3.0
Imoved my production 1.2 CRM database to a test server and proved it was running. I then upgraded to 3.0 but failed with the following error? Can anyone help? 22:06:53| Info| Drop merge publication in MSCRM database 22:07:32| Info| Upgrading metadata schema to add new tables and columns 22:07:33| Error| Install exception.System.Exception: Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed. ---> System.Data.SqlClient.SqlException: Cannot add columns to table 'Attribute' because it is being published for merge replication. at System.Data.SqlClient.SqlCommand...