| Home |
| Search |
| Today's Posts |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Worksheet Command Button will not work | Excel Worksheet Functions | |||
| VBA code behind command button | Excel Worksheet Functions | |||
| Check box making a command button work | Excel Programming | |||
| Assign Macro to Command Button doesn't work | Excel Programming | |||
| macro code doesnt work in command button | Excel Programming | |||
Threaded Mode
