Making cells have the same behaviour as control textbox based on dropdown list

[maxipoblete]

Hello Masters,

I hope you can help me with this one. Im updating an old Excel sheet with some macros (.xls) to a newer format (.xlsm). I have very little experience with Visual Basic and Macros.

The problem is that i want to create an updated sheet that works both in Mac OS and Windows , but the old sheet has 4 Active X Control Textboxes, that change based on the selected options of different Form Control Dropdown Lists. I heard that Form Controls work on Mac OS, so my only problem is to figure out an alternative to the ActiveX Control Textboxes.


Here is the main Dropdown List. As you can see it has four different options.

Those Options change the following text boxes (and also change the dropdown list shown, but thats not relevant). As you can see, the Text boxes are "Disabled" and only show values associated to the Dropdown list above them (Cell C43 approx)

But changing to the "Especial Soldado" option on the main Dropdown list ( first picture ):

It Enables the Text Boxes:

The idea is to have two "modes". The first one is to display pre-established values that are stored in another sheet on the same book. But the second "mode"(selecting "Especial Soldado" on the main Dropdown list) is to allow the user to input the data.

So , can this be done without using Active X controls?


Heres the code.

Option Explicit

Sub Listadesplegable224_AlCambiar()
Dim num As Single
Dim i, j As Integer
Dim k, m As Integer
Dim condicion1, condicion2, condicion3 As Boolean

num = Worksheets("I|O").Range("C13")
condicion1 = True
condicion2 = True
condicion3 = True

i = 1
j = 1

k = 7
m = 7

With Worksheets("I|O").DropDowns("Lista desplegable 227")
    .ListFillRange = " "
    Select Case num
        Case 1
            Do While condicion1
                .List(i) = Worksheets("Perfiles H ICHA 2001").Cells(k, 1).Value
               
                If Worksheets("Perfiles H ICHA 2001").Cells(k + 1, 1) = 0 Then
                    condicion1 = False
                End If
                i = i + 1
                k = k + 1
            Loop
            Worksheets("I|O").TextBox1.BackColor = RGB(255, 255, 255)
            Worksheets("I|O").TextBox2.BackColor = RGB(255, 255, 255)
            Worksheets("I|O").TextBox3.BackColor = RGB(255, 255, 255)
            Worksheets("I|O").TextBox4.BackColor = RGB(255, 255, 255)
                        
            Worksheets("I|O").TextBox1.Enabled = False
            Worksheets("I|O").TextBox2.Enabled = False
            Worksheets("I|O").TextBox3.Enabled = False
            Worksheets("I|O").TextBox4.Enabled = False
            
            Worksheets("I|O").TextBox1.ForeColor = RGB(0, 0, 0)
            Worksheets("I|O").TextBox2.ForeColor = RGB(0, 0, 0)
            Worksheets("I|O").TextBox3.ForeColor = RGB(0, 0, 0)
            Worksheets("I|O").TextBox4.ForeColor = RGB(0, 0, 0)
                                              
        Case 2
            Do While condicion2
                .List(j) = Worksheets("Perfiles H AISC").Cells(m, 1).Value

                If Worksheets("Perfiles H AISC").Cells(m + 1, 1) = 0 Then
                    condicion2 = False
                End If
                j = j + 1
                m = m + 1
            Loop
            Worksheets("I|O").TextBox1.BackColor = RGB(255, 255, 255)
            Worksheets("I|O").TextBox2.BackColor = RGB(255, 255, 255)
            Worksheets("I|O").TextBox3.BackColor = RGB(255, 255, 255)
            Worksheets("I|O").TextBox4.BackColor = RGB(255, 255, 255)
                        
            Worksheets("I|O").TextBox1.Enabled = False
            Worksheets("I|O").TextBox2.Enabled = False
            Worksheets("I|O").TextBox3.Enabled = False
            Worksheets("I|O").TextBox4.Enabled = False
            
            Worksheets("I|O").TextBox1.ForeColor = RGB(0, 0, 0)
            Worksheets("I|O").TextBox2.ForeColor = RGB(0, 0, 0)
            Worksheets("I|O").TextBox3.ForeColor = RGB(0, 0, 0)
            Worksheets("I|O").TextBox4.ForeColor = RGB(0, 0, 0)
           
        Case 3
            .ListFillRange = "Especial"
            Worksheets("I|O").TextBox1.BackColor = RGB(234, 241, 221)
            Worksheets("I|O").TextBox2.BackColor = RGB(234, 241, 221)
            Worksheets("I|O").TextBox3.BackColor = RGB(234, 241, 221)
            Worksheets("I|O").TextBox4.BackColor = RGB(234, 241, 221)
                        
            Worksheets("I|O").TextBox1.Enabled = True
            Worksheets("I|O").TextBox2.Enabled = True
            Worksheets("I|O").TextBox3.Enabled = True
            Worksheets("I|O").TextBox4.Enabled = True
            
            Worksheets("I|O").TextBox1.ForeColor = RGB(0, 112, 195)
            Worksheets("I|O").TextBox2.ForeColor = RGB(0, 112, 195)
            Worksheets("I|O").TextBox3.ForeColor = RGB(0, 112, 195)
            Worksheets("I|O").TextBox4.ForeColor = RGB(0, 112, 195)
            
            Worksheets("I|O").TextBox1.Value = 0
            Worksheets("I|O").TextBox2.Value = 0
            Worksheets("I|O").TextBox3.Value = 0
            Worksheets("I|O").TextBox4.Value = 0
            
        Case 4
            Do While condicion3
                .List(i) = Worksheets("Perfiles H ICHA 2008").Cells(k, 1).Value
               
                If Worksheets("Perfiles H ICHA 2008").Cells(k + 1, 1) = 0 Then
                    condicion3 = False
                End If
                i = i + 1
                k = k + 1
            Loop
            Worksheets("I|O").TextBox1.BackColor = RGB(255, 255, 255)
            Worksheets("I|O").TextBox2.BackColor = RGB(255, 255, 255)
            Worksheets("I|O").TextBox3.BackColor = RGB(255, 255, 255)
            Worksheets("I|O").TextBox4.BackColor = RGB(255, 255, 255)
                        
            Worksheets("I|O").TextBox1.Enabled = False
            Worksheets("I|O").TextBox2.Enabled = False
            Worksheets("I|O").TextBox3.Enabled = False
            Worksheets("I|O").TextBox4.Enabled = False
            
            Worksheets("I|O").TextBox1.ForeColor = RGB(0, 0, 0)
            Worksheets("I|O").TextBox2.ForeColor = RGB(0, 0, 0)
            Worksheets("I|O").TextBox3.ForeColor = RGB(0, 0, 0)
            Worksheets("I|O").TextBox4.ForeColor = RGB(0, 0, 0)
                        
    End Select
    
    Worksheets("I|O").TextBox1.Value = Worksheets("I|O").Cells(44, 14).Value
    Worksheets("I|O").TextBox2.Value = Worksheets("I|O").Cells(45, 14).Value
    Worksheets("I|O").TextBox3.Value = Worksheets("I|O").Cells(46, 14).Value
    Worksheets("I|O").TextBox4.Value = Worksheets("I|O").Cells(47, 14).Value
    
End With
End Sub

Thank you!!!