Macro Button

[davor]

Hi

I am new at macros and VBA editing.
I wanted to split txt form numbers in cells so I putted this code in module:

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby20150306
Dim xLen As Long
Dim xStr As String


xLen = VBA.Len(pWorkRng.Value)


For i = 1 To xLen
xStr = VBA.Mid(pWorkRng.Value, i, 1)
If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
SplitText = SplitText + xStr
End If
Next
End Function

The code worked but I wanted to have a button that would be on my ribbon. When I want to add it I can't see this macro in the macro list. All others are there.

What am I doing wrong???

 

[steve the fish]

Thats because its a function not a sub. It would have to be rewritten for use with a button.

 

[Yongle]

Functions do not appear in list of macros
Use like any other formula like this

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Text[/td][td]Value[/td][td]Formula in B2 copy down[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]ABC_123[/td][td]123[/td][td]=SplitText(A2,TRUE)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]XYZ_789[/td][td]789[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]

Or call from a procedure like this

Sub davor()
    Dim Cell As Range, aStr As String, x As Double
    For Each Cell In Range("A2:A3")
        [COLOR=#ff0000]x = SplitText(Cell, True)[/COLOR]
        aStr = aStr & Cell & " : " & x & vbCr
    Next Cell
    MsgBox aStr, , "Numbers Only"
End Sub

With values above, message box returns:
Numbers Only
ABC_123 : 123
XYZ_789 : 789

 

[Yongle]

or call from a button like this to get numbers in active cell

Private Sub CommandButton1_Click()
    MsgBox SplitText(ActiveCell, True)
End Sub

If none of the above gives you what you want, explain exactly how you want the button to work
(- what is the range ? where are returned values placed ? )

 

[davor]

or call from a button like this to get numbers in active cell

Private Sub CommandButton1_Click()
    MsgBox SplitText(ActiveCell, True)
End Sub

If none of the above gives you what you want, explain exactly how you want the button to work
(- what is the range ? where are returned values placed ? )

You solved it. Thank you very much