VBA Program

R

Rudy

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...
 
J

JLGWhiz

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.
 
J

Jim Cone

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...
 
R

Rudy

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
 
D

Don Guillett

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
 
R

Rudy

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
 
J

JLGWhiz

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.
 
Top