code for command button not work
 

Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of event or procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)

Application.EnableEvents = True
End If
End Sub


thanks

code for command button not work
 

Simon,
You cannot create any event declaration you like; it has to be what the
object is expecting.
It is easier to get VBA to generate the stub for you, so add a command
button, then double click it. Excel/VBA will generate the required _Click
outline for you.

You will see that you do not have any arguments, only empty brackets.
Therefore, you need to ActiveCell in you code instead of Target.

NickHK

"simon" wrote in message
...
Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of event or procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)

Application.EnableEvents = True
End If
End Sub


thanks

code for command button not work
 

You mean this?
Private Sub CommandButton1_Click()
Dim Temp As Variant

If activecell.Column = 1 Then
Application.EnableEvents = False
Temp = Split(activecell.Value, "^")

activecell.Value = Mid(Temp(0), 2)
activecell.Offset(0, 1).Value = Temp(1)
activecell.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If

End Sub


"NickHK" wrote:

Simon,
You cannot create any event declaration you like; it has to be what the
object is expecting.
It is easier to get VBA to generate the stub for you, so add a command
button, then double click it. Excel/VBA will generate the required _Click
outline for you.

You will see that you do not have any arguments, only empty brackets.
Therefore, you need to ActiveCell in you code instead of Target.

NickHK

"simon" wrote in message
...
Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of event or procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)

Application.EnableEvents = True
End If
End Sub


thanks

code for command button not work
 

code not work


"simon" wrote:

You mean this?
Private Sub CommandButton1_Click()
Dim Temp As Variant

If activecell.Column = 1 Then
Application.EnableEvents = False
Temp = Split(activecell.Value, "^")

activecell.Value = Mid(Temp(0), 2)
activecell.Offset(0, 1).Value = Temp(1)
activecell.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If

End Sub


"NickHK" wrote:

Simon,
You cannot create any event declaration you like; it has to be what the
object is expecting.
It is easier to get VBA to generate the stub for you, so add a command
button, then double click it. Excel/VBA will generate the required _Click
outline for you.

You will see that you do not have any arguments, only empty brackets.
Therefore, you need to ActiveCell in you code instead of Target.

NickHK

"simon" wrote in message
...
Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of event or procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)

Application.EnableEvents = True
End If
End Sub


thanks

code for command button not work
 

Well, an error number/description or some help from you would help.

NickHK

"simon" wrote in message
...
code not work


"simon" wrote:

You mean this?
Private Sub CommandButton1_Click()
Dim Temp As Variant

If activecell.Column = 1 Then
Application.EnableEvents = False
Temp = Split(activecell.Value, "^")

activecell.Value = Mid(Temp(0), 2)
activecell.Offset(0, 1).Value = Temp(1)
activecell.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If

End Sub


"NickHK" wrote:

Simon,
You cannot create any event declaration you like; it has to be what
the
object is expecting.
It is easier to get VBA to generate the stub for you, so add a command
button, then double click it. Excel/VBA will generate the required
_Click
outline for you.

You will see that you do not have any arguments, only empty brackets.
Therefore, you need to ActiveCell in you code instead of Target.

NickHK

"simon" wrote in message
...
Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of event or
procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)

Application.EnableEvents = True
End If
End Sub


thanks

code for command button not work
 

run-time error '9':
Subscript out of range

"NickHK" wrote:

Well, an error number/description or some help from you would help.

NickHK

"simon" wrote in message
...
code not work


"simon" wrote:

You mean this?
Private Sub CommandButton1_Click()
Dim Temp As Variant

If activecell.Column = 1 Then
Application.EnableEvents = False
Temp = Split(activecell.Value, "^")

activecell.Value = Mid(Temp(0), 2)
activecell.Offset(0, 1).Value = Temp(1)
activecell.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If

End Sub


"NickHK" wrote:

Simon,
You cannot create any event declaration you like; it has to be what
the
object is expecting.
It is easier to get VBA to generate the stub for you, so add a command
button, then double click it. Excel/VBA will generate the required
_Click
outline for you.

You will see that you do not have any arguments, only empty brackets.
Therefore, you need to ActiveCell in you code instead of Target.

NickHK

"simon" wrote in message
...
Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of event or
procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)

Application.EnableEvents = True
End If
End Sub


thanks

code for command button not work
 

That means you are trying to access an element in collection or array that
does not exist.
So it seems that "activecell.Value" does not contain what you think, i.e.. 3
"^" characters. You should check you have the correct values, maybe
If UBound(Temp)=2 Then 'OK

But you are not using the code posted below, because "activecell.Value"
would be "Activecell.Value".

NickHK

"simon" wrote in message
...
run-time error '9':
Subscript out of range

"NickHK" wrote:

Well, an error number/description or some help from you would help.

NickHK

"simon" wrote in message
...
code not work


"simon" wrote:

You mean this?
Private Sub CommandButton1_Click()
Dim Temp As Variant

If activecell.Column = 1 Then
Application.EnableEvents = False
Temp = Split(activecell.Value, "^")

activecell.Value = Mid(Temp(0), 2)
activecell.Offset(0, 1).Value = Temp(1)
activecell.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If

End Sub


"NickHK" wrote:

Simon,
You cannot create any event declaration you like; it has to be
what
the
object is expecting.
It is easier to get VBA to generate the stub for you, so add a
command
button, then double click it. Excel/VBA will generate the required
_Click
outline for you.

You will see that you do not have any arguments, only empty
brackets.
Therefore, you need to ActiveCell in you code instead of Target.

NickHK

"simon" wrote in message
...
Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of event or
procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)

Application.EnableEvents = True
End If
End Sub


thanks

code for command button not work
 

In fact, these code work well

Private Sub Worksheet_Activate()
Dim Temp As Variant
If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")
Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If
End Sub

The problem is that I can't put above code in button to work
or a form to read it into the cell i want.

"NickHK" wrote:

That means you are trying to access an element in collection or array that
does not exist.
So it seems that "activecell.Value" does not contain what you think, i.e.. 3
"^" characters. You should check you have the correct values, maybe
If UBound(Temp)=2 Then 'OK

But you are not using the code posted below, because "activecell.Value"
would be "Activecell.Value".

NickHK

"simon" wrote in message
...
run-time error '9':
Subscript out of range

"NickHK" wrote:

Well, an error number/description or some help from you would help.

NickHK

"simon" wrote in message
...
code not work


"simon" wrote:

You mean this?
Private Sub CommandButton1_Click()
Dim Temp As Variant

If activecell.Column = 1 Then
Application.EnableEvents = False
Temp = Split(activecell.Value, "^")

activecell.Value = Mid(Temp(0), 2)
activecell.Offset(0, 1).Value = Temp(1)
activecell.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If

End Sub


"NickHK" wrote:

Simon,
You cannot create any event declaration you like; it has to be
what
the
object is expecting.
It is easier to get VBA to generate the stub for you, so add a
command
button, then double click it. Excel/VBA will generate the required
_Click
outline for you.

You will see that you do not have any arguments, only empty
brackets.
Therefore, you need to ActiveCell in you code instead of Target.

NickHK

"simon" wrote in message
...
Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of event or
procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)

Application.EnableEvents = True
End If
End Sub


thanks

code for command button not work
 

Simon,
What is Target ?
Normally it does not exist an event like Worksheet_Activate.

Use ActiveCell instead, or specify the range e.g. Range("A1") etc

NickHK

"simon" wrote in message
...
In fact, these code work well

Private Sub Worksheet_Activate()
Dim Temp As Variant
If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")
Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If
End Sub

The problem is that I can't put above code in button to work
or a form to read it into the cell i want.

"NickHK" wrote:

That means you are trying to access an element in collection or array
that
does not exist.
So it seems that "activecell.Value" does not contain what you think,
i.e.. 3
"^" characters. You should check you have the correct values, maybe
If UBound(Temp)=2 Then 'OK

But you are not using the code posted below, because "activecell.Value"
would be "Activecell.Value".

NickHK

"simon" wrote in message
...
run-time error '9':
Subscript out of range

"NickHK" wrote:

Well, an error number/description or some help from you would help.

NickHK

"simon" wrote in message
...
code not work


"simon" wrote:

You mean this?
Private Sub CommandButton1_Click()
Dim Temp As Variant

If activecell.Column = 1 Then
Application.EnableEvents = False
Temp = Split(activecell.Value, "^")

activecell.Value = Mid(Temp(0), 2)
activecell.Offset(0, 1).Value = Temp(1)
activecell.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If

End Sub


"NickHK" wrote:

Simon,
You cannot create any event declaration you like; it has to be
what
the
object is expecting.
It is easier to get VBA to generate the stub for you, so add a
command
button, then double click it. Excel/VBA will generate the
required
_Click
outline for you.

You will see that you do not have any arguments, only empty
brackets.
Therefore, you need to ActiveCell in you code instead of
Target.

NickHK

"simon" wrote in message
...
Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of event or
procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)

Application.EnableEvents = True
End If
End Sub


thanks

code for command button not work
 

NickHK,
Sorry it should be worksheet_change as following code, but I want to try
merge these code in a command button, to turn on or off this read and splite
the read function.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Temp As Variant
If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")
Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If
End Sub

"NickHK" wrote:

Simon,
What is Target ?
Normally it does not exist an event like Worksheet_Activate.

Use ActiveCell instead, or specify the range e.g. Range("A1") etc

NickHK

"simon" wrote in message
...
In fact, these code work well

Private Sub Worksheet_Activate()
Dim Temp As Variant
If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")
Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If
End Sub

The problem is that I can't put above code in button to work
or a form to read it into the cell i want.

"NickHK" wrote:

That means you are trying to access an element in collection or array
that
does not exist.
So it seems that "activecell.Value" does not contain what you think,
i.e.. 3
"^" characters. You should check you have the correct values, maybe
If UBound(Temp)=2 Then 'OK

But you are not using the code posted below, because "activecell.Value"
would be "Activecell.Value".

NickHK

"simon" wrote in message
...
run-time error '9':
Subscript out of range

"NickHK" wrote:

Well, an error number/description or some help from you would help.

NickHK

"simon" wrote in message
...
code not work


"simon" wrote:

You mean this?
Private Sub CommandButton1_Click()
Dim Temp As Variant

If activecell.Column = 1 Then
Application.EnableEvents = False
Temp = Split(activecell.Value, "^")

activecell.Value = Mid(Temp(0), 2)
activecell.Offset(0, 1).Value = Temp(1)
activecell.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If

End Sub


"NickHK" wrote:

Simon,
You cannot create any event declaration you like; it has to be
what
the
object is expecting.
It is easier to get VBA to generate the stub for you, so add a
command
button, then double click it. Excel/VBA will generate the
required
_Click
outline for you.

You will see that you do not have any arguments, only empty
brackets.
Therefore, you need to ActiveCell in you code instead of
Target.

NickHK

"simon" wrote in message
...
Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of event or
procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)

Application.EnableEvents = True
End If
End Sub


thanks

code for command button not work
 

I mean turn on/off this function with button.

"NickHK" wrote:

Simon,
What is Target ?
Normally it does not exist an event like Worksheet_Activate.

Use ActiveCell instead, or specify the range e.g. Range("A1") etc

NickHK

"simon" wrote in message
...
In fact, these code work well

Private Sub Worksheet_Activate()
Dim Temp As Variant
If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")
Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If
End Sub

The problem is that I can't put above code in button to work
or a form to read it into the cell i want.

"NickHK" wrote:

That means you are trying to access an element in collection or array
that
does not exist.
So it seems that "activecell.Value" does not contain what you think,
i.e.. 3
"^" characters. You should check you have the correct values, maybe
If UBound(Temp)=2 Then 'OK

But you are not using the code posted below, because "activecell.Value"
would be "Activecell.Value".

NickHK

"simon" wrote in message
...
run-time error '9':
Subscript out of range

"NickHK" wrote:

Well, an error number/description or some help from you would help.

NickHK

"simon" wrote in message
...
code not work


"simon" wrote:

You mean this?
Private Sub CommandButton1_Click()
Dim Temp As Variant

If activecell.Column = 1 Then
Application.EnableEvents = False
Temp = Split(activecell.Value, "^")

activecell.Value = Mid(Temp(0), 2)
activecell.Offset(0, 1).Value = Temp(1)
activecell.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If

End Sub


"NickHK" wrote:

Simon,
You cannot create any event declaration you like; it has to be
what
the
object is expecting.
It is easier to get VBA to generate the stub for you, so add a
command
button, then double click it. Excel/VBA will generate the
required
_Click
outline for you.

You will see that you do not have any arguments, only empty
brackets.
Therefore, you need to ActiveCell in you code instead of
Target.

NickHK

"simon" wrote in message
...
Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of event or
procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)

Application.EnableEvents = True
End If
End Sub


thanks

code for command button not work
 

Simon,

You can have the button set a variable or a cell to a specific value then
test for that in the -Change event.

<Module code
Dim RunSplit as boolean
<Module code

<CommandButton
Private Sub CommandButton1_Click()
RunSplit=Not RunSplit
'Or range("RunSplit").Value=Not range("RunSplit").Value
End Sub
</CommandButton

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Temp As Variant
If RunSplit=True Then
If Target.Column = 1 Then
Application.EnableEvents = False
.... etc

NickHK

"simon" wrote in message
...
NickHK,
Sorry it should be worksheet_change as following code, but I want to try
merge these code in a command button, to turn on or off this read and
splite
the read function.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Temp As Variant
If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")
Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If
End Sub

"NickHK" wrote:

Simon,
What is Target ?
Normally it does not exist an event like Worksheet_Activate.

Use ActiveCell instead, or specify the range e.g. Range("A1") etc

NickHK

"simon" wrote in message
...
In fact, these code work well

Private Sub Worksheet_Activate()
Dim Temp As Variant
If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")
Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If
End Sub

The problem is that I can't put above code in button to work
or a form to read it into the cell i want.

"NickHK" wrote:

That means you are trying to access an element in collection or
array
that
does not exist.
So it seems that "activecell.Value" does not contain what you think,
i.e.. 3
"^" characters. You should check you have the correct values, maybe
If UBound(Temp)=2 Then 'OK

But you are not using the code posted below, because
"activecell.Value"
would be "Activecell.Value".

NickHK

"simon" wrote in message
...
run-time error '9':
Subscript out of range

"NickHK" wrote:

Well, an error number/description or some help from you would
help.

NickHK

"simon" wrote in message
...
code not work


"simon" wrote:

You mean this?
Private Sub CommandButton1_Click()
Dim Temp As Variant

If activecell.Column = 1 Then
Application.EnableEvents = False
Temp = Split(activecell.Value, "^")

activecell.Value = Mid(Temp(0), 2)
activecell.Offset(0, 1).Value = Temp(1)
activecell.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) -
1)
Application.EnableEvents = True
End If

End Sub


"NickHK" wrote:

Simon,
You cannot create any event declaration you like; it has
to be
what
the
object is expecting.
It is easier to get VBA to generate the stub for you, so
add a
command
button, then double click it. Excel/VBA will generate the
required
_Click
outline for you.

You will see that you do not have any arguments, only
empty
brackets.
Therefore, you need to ActiveCell in you code instead of
Target.

NickHK

"simon" wrote in message
...
Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of
event or
procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) -
1)

Application.EnableEvents = True
End If
End Sub


thanks

code for command button not work
 

what is the Module code ? and
the commandbutton ?


"NickHK" wrote:

Simon,

You can have the button set a variable or a cell to a specific value then
test for that in the -Change event.

<Module code
Dim RunSplit as boolean
<Module code

<CommandButton
Private Sub CommandButton1_Click()
RunSplit=Not RunSplit
'Or range("RunSplit").Value=Not range("RunSplit").Value
End Sub
</CommandButton

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Temp As Variant
If RunSplit=True Then
If Target.Column = 1 Then
Application.EnableEvents = False
.... etc

NickHK

"simon" wrote in message
...
NickHK,
Sorry it should be worksheet_change as following code, but I want to try
merge these code in a command button, to turn on or off this read and
splite
the read function.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Temp As Variant
If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")
Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If
End Sub

"NickHK" wrote:

Simon,
What is Target ?
Normally it does not exist an event like Worksheet_Activate.

Use ActiveCell instead, or specify the range e.g. Range("A1") etc

NickHK

"simon" wrote in message
...
In fact, these code work well

Private Sub Worksheet_Activate()
Dim Temp As Variant
If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")
Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
Application.EnableEvents = True
End If
End Sub

The problem is that I can't put above code in button to work
or a form to read it into the cell i want.

"NickHK" wrote:

That means you are trying to access an element in collection or
array
that
does not exist.
So it seems that "activecell.Value" does not contain what you think,
i.e.. 3
"^" characters. You should check you have the correct values, maybe
If UBound(Temp)=2 Then 'OK

But you are not using the code posted below, because
"activecell.Value"
would be "Activecell.Value".

NickHK

"simon" wrote in message
...
run-time error '9':
Subscript out of range

"NickHK" wrote:

Well, an error number/description or some help from you would
help.

NickHK

"simon" wrote in message
...
code not work


"simon" wrote:

You mean this?
Private Sub CommandButton1_Click()
Dim Temp As Variant

If activecell.Column = 1 Then
Application.EnableEvents = False
Temp = Split(activecell.Value, "^")

activecell.Value = Mid(Temp(0), 2)
activecell.Offset(0, 1).Value = Temp(1)
activecell.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) -
1)
Application.EnableEvents = True
End If

End Sub


"NickHK" wrote:

Simon,
You cannot create any event declaration you like; it has
to be
what
the
object is expecting.
It is easier to get VBA to generate the stub for you, so
add a
command
button, then double click it. Excel/VBA will generate the
required
_Click
outline for you.

You will see that you do not have any arguments, only
empty
brackets.
Therefore, you need to ActiveCell in you code instead of
Target.

NickHK

"simon" wrote in message
...
Hi,
every time compile code get error:

compile error:

procedure declaration does not match description of
event or
procedure
having the same name

code in commmandbutton1:
Private Sub CommandButton1_Click(ByVal target As Range)

Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) -
1)

Application.EnableEvents = True
End If
End Sub


thanks