Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA 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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Program
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. "Rudy" wrote: 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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Program
The code you posted is incomplete.
Please try again. Also, what module contains the code - the sheet module or a standard module? -- Jim 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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Program
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 "Jim Cone" wrote: The code you posted is incomplete. Please try again. Also, what module contains the code - the sheet module or a standard module? -- Jim 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... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Program
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 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Rudy" wrote in message ... 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 "Jim Cone" wrote: The code you posted is incomplete. Please try again. Also, what module contains the code - the sheet module or a standard module? -- Jim 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... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Program
You didn't answer my questions posed to try to help you.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Rudy" wrote in message ... 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 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Rudy" wrote in message ... 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 "Jim Cone" wrote: The code you posted is incomplete. Please try again. Also, what module contains the code - the sheet module or a standard module? -- Jim 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... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Program
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. "Rudy" wrote: 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 "Jim Cone" wrote: The code you posted is incomplete. Please try again. Also, what module contains the code - the sheet module or a standard module? -- Jim 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to unload the loaded XLL file in Excel? Hi all, I amdebugging XLL link library using Visual C++. Everytime I rebuild the XLL, Ihave to close the whole Excel program and relaunch the Excel program again,and then load in the newly gene | Excel Discussion (Misc queries) | |||
Program | Excel Discussion (Misc queries) | |||
Program | Excel Programming | |||
merging excel program with tdc finance program | Excel Programming |