code for command button not work

[Guest]

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

 

[NickHK]

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

 

[Guest]

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

 

[Guest]

code 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]

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

NickHK

 

[Guest]

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

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

NickHK

 

[NickHK]

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

 

[Guest]

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]

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

 

[Guest]

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

 

[Guest]

I mean turn on/off this function with button.

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

 

[NickHK]

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

 

[Guest]

what is the Module code ? and
the commandbutton ?