How to put the VBA code contained in code module in sheet module

I have a button in the "code module" which runs a request. Next to the Button is a label included which shows a check mark a soon as the button has finished running.

The button code is in the code modul. The codes for the label with the check mark is inlcuded in the workbook and sheet modul.

Now, the issue is when I push the button it runs perfectly fine and does what it supposed to but the label with the check mark does not get activated. The reason might be because I have not included/referenced the workbook/sheet modul in my code modul. Hope for a bit help.

Code in workbook Module:

Option Explicit
Private Sub Workbook_Open()
Call Tabelle1.prcResetLabels
End Sub


Code in Sheet Module:

Option Explicit

Private Sub Schaltfläche2_Klicken()
Call prcSetLabel(probjLabel:=Label1)
End Sub

Private Sub prcSetLabel(ByRef probjLabel As MSForms.Label)
With probjLabel
.Caption = "P"
End With
End Sub

Friend Sub prcResetLabels()
Dim objOLEObject As OLEObject
For Each objOLEObject In OLEObjects
With objOLEObject
If .progID = "Forms.Label.1" Then _
.Object.Caption = vbNullString
End With
Next
End Sub


Code in Codemodul:

Public Sub Schaltfläche2_Klicken()
With Sheets("Table1")
.Range("A1").End(xlUp).Offset(1, 0).Value = Environ("USERNAME")
End With
End Sub


1 Answer
1

The answer ist simple this:

Sub Schaltfläche2_Klicken()
Call prcResetLabels
With Sheets("Table1")
.Range("A1").End(xlUp).Offset(1, 0).Value = Environ("USERNAME")
End With
Call prcSetLabel(probjLabel:=Table1.Label1)
End Sub

Private Sub prcSetLabel(ByVal probjLabel As Object)
With probjLabel
.Object.Caption = "P"
End With
End Sub
Public Sub prcResetLabels()
Dim objOLEObject As OLEObject

For Each objOLEObject In Table1.OLEObjects
With objOLEObject
If .progID = "Forms.Label.1" Then _
.Object.Caption = vbNullString
End With
Next
End Sub


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.