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

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

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

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

VBA Program
 

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

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

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

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