PDA

View Full Version : [SOLVED] callback an EditBox in the Ribbon



cpmsimoes
02-14-2017, 10:11 AM
Hi,

I'm trying to link the EditBox value in a personal ribbon to a macro (file in attach). I'm a basic VBA user, the codes were found on internet and adapted, however something is missing and I have no clue!?

Anybody can help?

Thanks
cpmsimoes

Paul_Hossler
02-14-2017, 11:45 AM
That's good for "a basic VBA user"

I think you over-complicated it: too many variables and 99% identical subs

I changed your CustomUI



<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="MyTab" label="Operators" >
<group id="MyGroup" label="Group Operators">
<editBox id="EditBox" label="Without Label" onChange="SetNumberValue" sizeString="WWWWWW" showLabel="false"/>
<box id="box1" boxStyle="horizontal">
<button id="bMultiply" label="X" size="normal" onAction="Action" />
<button id="bDivide" label="/" size="normal" onAction="Action" />
</box>
<box id="box2" boxStyle="horizontal">
<button id="bAdd" label="+" size="normal" onAction="Action" />
<button id="bSubtract" label="-" size="normal" onAction="Action" />
</box>
</group>
</tab>
</tabs>
</ribbon>
</customUI>


and simplified your callbacks



Option Explicit

Public RibbonTextBox As String

'Callback for EditBox onChange
Sub SetNumberValue(control As IRibbonControl, text As String)
RibbonTextBox = text
End Sub

'Callback for bMultiple, etc. onAction
Sub Action(control As IRibbonControl)
Dim xValue As Double
Dim c As Range

If Not TypeOf Selection Is Range Then Exit Sub

If Not IsNumeric(RibbonTextBox) Then
MsgBox "Error! The value entered '" & RibbonTextBox & " is NOT a NUMBER."
Exit Sub
End If

xValue = CDbl(RibbonTextBox)

For Each c In Selection.Cells
If IsNumeric(c.Value) Then
Select Case control.ID
Case "bMultiply": c.Value = c.Value * xValue
Case "bDivide": c.Value = c.Value / xValue
Case "bAdd": c.Value = c.Value + xValue
Case "bSubtract": c.Value = c.Value - xValue
Case Else
MsgBox control.ID & " not found"
End Select
End If
Next c

End Sub


so give this a shot

cpmsimoes
02-14-2017, 04:37 PM
Wow! Thank you so much, Paul. It's working like I wanted. Great!!