 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / October 2008VBA Program
Hi, There is this VBA program in my excel sheet that tells excel to : Function MATHEMATICS() IF C14="C" N14 TO M14 End Function If I am the one opening up this spreadsheet & type in the letter "C" on column C14, the value in column "N14" automatically transposes on column "O14". However, if someone else has opened this spreadsheet & type in "C" on column C14, the value in N14 does not automatically transposes on column "O14". Pls help as I am going to handover this job to another person but if this problem cannot be resolved, he has to do the old fashion way. Thanks... There is probably a worksheet_change event code behind the worksheet. Right click the sheet tab and then click view code in the drop doiwn menu that appears. Look for a macro that starst like this: Private Sub Worksheet_Change(ByVal Target As Range) Then look for a line of code that looks something like: If UserName = "YourName" Then MATHEMATICS With YourName being whatever your name is registered as in your PC. This would indicate that there is a macro that runs when you, and only you, enter the "C" in the specified cell. If so, then that macro would have to be modified to use the new user's name. > Hi, > [quoted text clipped - 11 lines] > "O14". Pls help as I am going to handover this job to another person but if > this problem cannot be resolved, he has to do the old fashion way. Thanks... The code you posted is incomplete. Please try again. Also, what module contains the code - the sheet module or a standard module?  SignatureJim Cone Portland, Oregon USA "Rudy" wrote in message Hi, There is this VBA program in my excel sheet that tells excel to : Function MATHEMATICS() IF C14="C" N14 TO M14 End Function If I am the one opening up this spreadsheet & type in the letter "C" on column C14, the value in column "N14" automatically transposes on column "O14". However, if someone else has opened this spreadsheet & type in "C" on column C14, the value in N14 does not automatically transposes on column "O14". Pls help as I am going to handover this job to another person but if this problem cannot be resolved, he has to do the old fashion way. Thanks... Hi, This is the sheet module Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo endit If Intersect(Target, Range("C12:C500")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each Cell In Target If Target.Value = "C" And Target.Offset(0, 11) _ .Value <> "" Then With Target .Offset(0, 12).Value = Target.Offset(0, 11).Value .Offset(0, 11).ClearContents End With End If Next endit: Application.EnableEvents = True End Sub Private Sub Worksheet_BeforeRightClick _ (ByVal Target As Excel.Range, Cancel As Boolean) If IsNumeric(Target) And Not IsEmpty(Target) Then Application.Dialogs(xlDialogFormatNumber).Show Cancel = True End If End Sub > The code you posted is incomplete. > Please try again. > Also, what module contains the code - the sheet module or a standard module? To answer your question, it could have been as simple as the other user typing lower case c instead of C. But, if your goal is to move col N to col O WHEN c or C is entered in col C then this may be more efficient. Why did you have a for each loop for ONE selection? Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C12:C500")) Is Nothing Then Exit Sub Application.EnableEvents = False If UCase(Target) = "C" And Target.Offset(, 11) <> "" Then Target.Offset(, 11).Cut Destination:=Target.Offset(, 12) End If Application.EnableEvents = True End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hi, > [quoted text clipped - 29 lines] >> Also, what module contains the code - the sheet module or a standard >> module? Hi Don, The other user is also typing in upper case "C". This VBA was just given to me on this forum some few months back. To be honest, I am not really well verse with VBA, still a beginner with zero background on VBA. Just hoping to solicit help from you guys.. thanks, rudy > To answer your question, it could have been as simple as the other user > typing lower case c instead of C. But, if your goal is to move col N to col [quoted text clipped - 43 lines] > >> Also, what module contains the code - the sheet module or a standard > >> module? You didn't answer my questions posed to try to help you. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hi Don, > [quoted text clipped - 58 lines] >> >> Also, what module contains the code - the sheet module or a standard >> >> module? Rudy, there is no reason, based on the worksheet_change code, why any other user could not enter a "C" in any cell of Range("C12:C500") and the code would not execute. Although, as Don pointed out, the code itself could be more efficiently written. One thing you could do, is change this line from: If Target.Value = "C" And Target.Offset(0, 11) _ .Value <> "" Then To: If UCase(Target.Value) = "C" And Target.Offset(0, 11) _ .Value <> "" Then That way, it would execute for "c" or "C". Also, the other user could have been trying to enter the "C" in rows 1 thru 11, which the code would have ignored. The executable range begins in row 12. > Hi, > [quoted text clipped - 28 lines] > > Please try again. > > Also, what module contains the code - the sheet module or a standard module? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.