Change object properties for previously created objects

[blukrr]

Hello folks,

I would like to do basically what the title says: Change object properties for previously created objects.

I have a table in column A of a worksheet named Codes full of itens, lets say:

120
121
122
123

I also have buttons named Button120, Button121, Button122 and Button123.

I made this code that does a FOR for each button in the table above.
Now I need to pass the button name so that it will change the button I should be changing the propeties every time the FOR loops.

Something like this.


TotalData = Worksheets("Codes").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
For i = 1 To TotalData
CurrentBut = "Button" & Worksheets("Codes").Cells(i, "A").Value
With CurrentBut
.Enabled = False
.Caption = CurrentBut
End With
Next i


But somehow this code won't work because I can't assign the With function a variable object.

is there a way of doing this?

Thanks in advance or any tips.

[Kenneth Hobs]

You did not set an object. Using With for a string errors as one expects.

This works for an ActiveX command button but fails for the Caption since that object has no Caption property.

Option Explicit

Sub ken()
Dim TotalData As Integer, i As Integer, CurrentBut As Object
TotalData = Worksheets("Codes").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
For i = 1 To TotalData
Set CurrentBut = Worksheets("Codes").OLEObjects("Button" & Worksheets("Codes").Cells(i, "A").Value)
With CurrentBut
.Enabled = True
.Caption = CurrentBut
End With
Next i
End Sub

[blukrr]

Oh I think i got it what you mean.

I tried the code, I think I forgot to mention that the button is in a Userform.

I guess I can't use the OLEObjects for it.

I tried using CommmandButton but it didn't work.

[Kenneth Hobs]

When solving problems, I like to say that half the solution is defining the problem. You will find that more exact solutions are found if you post short example files.

Obviously, this code can fail to meet expectations but it does solve the problem in the scope of your latest details.

Private Sub OKBttn_Click()
Dim TotalData As Integer, i As Integer, CurrentBut As Object
TotalData = Worksheets("Codes").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
For i = 1 To TotalData
Set CurrentBut = UserForm1.Controls("Button" & Worksheets("Codes").Cells(i, "A").Value)
With CurrentBut
.Enabled = False
.Caption = "Button" & Worksheets("Codes").Cells(i, "A").Value
End With
Next i
End Sub

[blukrr]

Thanks!

this totally worked, sometimes it's nice to go back to the basics.