CODEWITHSUNDEEP
vbaexcel
jvh
jvh

Reputation: 143

Can't use objects from a worksheet when the worksheet is declared

So The following works

Sheet1.btnAfkeurMin.Visible = True

Below is what doesn't work

Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
    If cmbDate.Value = WS.Name Then
         WS.btnAfkeurMin.Visible = True
    end if
next

cmdDate.Value and WS.Name are the same. (Checked in console) How do I call btnAfkeurMin while using WS?

Upvotes: 1

Views: 38

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Use

WS.OLEObjects("btnAfkeurMin").Visible = True

If you want to use the Object directly then you can also use this

Worksheets(WS.Name).btnAfkeurMin.Visible = True

Upvotes: 0

Soulfire
Soulfire

Reputation: 4296

I suspect the issue is caused because btnAfkeurmin is only assigned once. As you loop through the worksheets, you need to re-reference the new button on the new sheet.

For example, this code will loop through each sheet and hide all of the buttons named btnAfkeurmin.

Option Explicit

Sub TestFormControls()

        Dim WS As Worksheet

        For Each WS In ThisWorkbook.Worksheets
                WS.Shapes("btnAfkeurMin").Visible = msoFalse
        Next WS

End Sub

Adapting to your code:

Option Explicit

Sub TestFormControls()
        Dim WS As Worksheet

        For Each WS In ThisWorkbook.Worksheets
            If cmbDate.Value = WS.Name Then
                 WS.Shapes("btnAfkeurMin").Visible = True
            End If
        Next

End Sub

Similar logic applies if you need to reference different comboboxes named cmbDate. If you are always referencing the single one that is declared earlier in the code, this is fine (which I gather is what's happening based on your statement that cmbDate.Value = WS.Name evaluates to true).

But if, for some reason, you had a different combobox on each sheet that you needed to reference you would do so in the same way with:

WS.Shapes("cmbDate").Value

Upvotes: 4

Related Questions