Result of a cell in an active X button

[Romano_odK]

Good afternoon,

Is it possible to put cell that is calculated as in an active X button and if so how can I accomplish this?

Thank you for your responce,

Romano

 

[GWteB]

If you mean show the result of the calculation on the button's surface, the use something like this ...

CommandButton1.Caption = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value

 

[Romano_odK]

If you mean show the result of the calculation on the button's surface, the use something like this ...

CommandButton1.Caption = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value

Good afternoon.
Yes this is what I meant, now it needs to refresh automatically. Can this be done also?

Thank you for your time.

Tomano

 

[GWteB]

You could use the calculate event for that. How the code exactly would look like depends on where your command button is located and on which worksheet the intended text is stored.
Either way, I would use the event handler on workbook level for that. In case both button and text are on the same worksheet the code could look like this ...

To be pasted in the ThisWorkbook module:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If Sh.Name = "Sheet1" Then
         Sh.OLEObjects("CommandButton1").Object.Caption = Sh.Range("A1").Value
    End If
End Sub

In case your button is located on a userform a valid reference to that userform is needed, so the code that launches such a form has to be written in a way it takes that into account. In such a case I suggest to write a custom userform property for that rather than getting direct access to a userform's control.

 

[Romano_odK]

You could use the calculate event for that. How the code exactly would look like depends on where your command button is located and on which worksheet the intended text is stored.
Either way, I would use the event handler on workbook level for that. In case both button and text are on the same worksheet the code could look like this ...

To be pasted in the ThisWorkbook module:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If Sh.Name = "Sheet1" Then
         Sh.OLEObjects("CommandButton1").Object.Caption = Sh.Range("A1").Value
    End If
End Sub

In case your button is located on a userform a valid reference to that userform is needed, so the code that launches such a form has to be written in a way it takes that into account. In such a case I suggest to write a custom userform property for that rather than getting direct access to a userform's control.

Thank you for your reply. This is what I was looking for.

Romano

 

[GWteB]

You're welcome and thanks for letting me know.