Combobox VBA

[Ivan_Pzdorvan]

	1	2	3	4
Ivan
Jon
Stiven			?​
Lex

I have 3 combobox. Name= А, Day= В
combobox1=3
combobox2=Stiven
write x in cell (combobox1,combobox2)

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

To try to solve your doubt. I present you with two options, the first, if both comboboxes are inside a userform.
The second option is if the comboboxes are activex controls and are on the sheet.

In the options, change the code "sheet1" to the name of your sheet where you have the data.

Option 1.
If your data is like this in your sheet, that is, starting in column A and row 1:

Dante Amor
	A	B	C	D	E	F
1		1	2	3	4
2	Ivan
3	Jon
4	Stiven			X
5	Lex
6
7
sheet1

The following code inside the userform:

Option Explicit

Private Sub ComboBox1_Change()
  Call ponerX
End Sub

Private Sub ComboBox2_Change()
  Call ponerX
End Sub

Sub ponerX()
  If ComboBox1.ListIndex > -1 And ComboBox2.ListIndex > -1 Then
    Sheets("sheet1").Cells(ComboBox1.ListIndex + 2, ComboBox1.ListIndex + 2).Value = "X"
  End If
End Sub

Private Sub UserForm_Activate()
  With Sheets("sheet1")
    ComboBox1.List = .Range("A2", .Range("A" & Rows.Count).End(3)).Value
    ComboBox2.List = Application.Transpose(.Range("B1", .Cells(1, Columns.Count).End(1)).Value)
  End With
End Sub

Option 2.
If the comboboxes are activex controls and are on the sheet.

The following code in the events of the sheet that has the information and the combos

Private Sub ComboBox1_Change()
  Call fillx
End Sub

Private Sub ComboBox2_Change()
  Call fillx
End Sub

Sub fillx()
  Dim f As Range
  Dim nRow As Long
 
  With Sheets("sheet1")
    If ComboBox1.ListIndex > -1 And ComboBox2.ListIndex > -1 Then
      Set f = Range("A:A").Find(ComboBox1.Value, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        nRow = f.Row
        Set f = Range("1:1").Find(ComboBox2.Value, , xlValues, xlWhole, , , False)
        If Not f Is Nothing Then
          .Cells(nRow, f.Column).Value = "X"
        End If
      End If
    End If
  End With
End Sub

Note:
If you have difficulties, please provide all the necessary information to help you: sheet name, combo names, if they are activex or in a userform, where is the data in your sheet and how do you load the data into the combos.

I hope the above helps you.
Respectfully
Dante Amor

 

[Ivan_Pzdorvan]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

To try to solve your doubt. I present you with two options, the first, if both comboboxes are inside a userform.
The second option is if the comboboxes are activex controls and are on the sheet.

In the options, change the code "sheet1" to the name of your sheet where you have the data.

Option 1.
If your data is like this in your sheet, that is, starting in column A and row 1:

Dante Amor
	A	B	C	D	E	F
1		1	2	3	4
2	Ivan
3	Jon
4	Stiven			X
5	Lex
6
7
sheet1

The following code inside the userform:

Option Explicit

Private Sub ComboBox1_Change()
  Call ponerX
End Sub

Private Sub ComboBox2_Change()
  Call ponerX
End Sub

Sub ponerX()
  If ComboBox1.ListIndex > -1 And ComboBox2.ListIndex > -1 Then
    Sheets("sheet1").Cells(ComboBox1.ListIndex + 2, ComboBox1.ListIndex + 2).Value = "X"
  End If
End Sub

Private Sub UserForm_Activate()
  With Sheets("sheet1")
    ComboBox1.List = .Range("A2", .Range("A" & Rows.Count).End(3)).Value
    ComboBox2.List = Application.Transpose(.Range("B1", .Cells(1, Columns.Count).End(1)).Value)
  End With
End Sub

Option 2.
If the comboboxes are activex controls and are on the sheet.

The following code in the events of the sheet that has the information and the combos

Private Sub ComboBox1_Change()
  Call fillx
End Sub

Private Sub ComboBox2_Change()
  Call fillx
End Sub

Sub fillx()
  Dim f As Range
  Dim nRow As Long
 
  With Sheets("sheet1")
    If ComboBox1.ListIndex > -1 And ComboBox2.ListIndex > -1 Then
      Set f = Range("A:A").Find(ComboBox1.Value, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        nRow = f.Row
        Set f = Range("1:1").Find(ComboBox2.Value, , xlValues, xlWhole, , , False)
        If Not f Is Nothing Then
          .Cells(nRow, f.Column).Value = "X"
        End If
      End If
    End If
  End With
End Sub

Note:
If you have difficulties, please provide all the necessary information to help you: sheet name, combo names, if they are activex or in a userform, where is the data in your sheet and how do you load the data into the combos.

I hope the above helps you.
Respectfully
Dante Amor

Місяць = sheet
День = В2:AF2
0,1,2,3,4,5,6 = A3:A24
in VBA
if День = 15 and 0 = Гулич when click buttom must P7 = X
sheet(combobox1.Value)

 

[DanteAmor]

Please, update the names of the comboboxes in the following code. Combobox1 for the sheet names, Combobox2 for days 1-31, and Combobox3 for the data you have in column A.
You can identify them by colors:

Private Sub CommandButton1_Click()
  Dim sh As Worksheet
  Dim f As Range
  Dim col As Long
  
  If ComboBox1.ListIndex > -1 And ComboBox2.ListIndex > -1 And ComboBox3.ListIndex > -1 Then
  
    Set sh = Sheets(ComboBox1.Value)                                        'sheet
    Set f = sh.Range("B2:AF2").Find(ComboBox2.Value, , xlValues, xlWhole)   '1 to 31
    If Not f Is Nothing Then
      col = f.Column
      Set f = sh.Range("A:A").Find(ComboBox3.Value, , xlValues, xlWhole)   'data in column A
      If Not f Is Nothing Then
        sh.Cells(f.Row, col).Value = "X"
      End If
    End If
  End If
End Sub

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor

 

[Ivan_Pzdorvan]

Please, update the names of the comboboxes in the following code. Combobox1 for the sheet names, Combobox2 for days 1-31, and Combobox3 for the data you have in column A.
You can identify them by colors:

Private Sub CommandButton1_Click()
  Dim sh As Worksheet
  Dim f As Range
  Dim col As Long
 
  If ComboBox1.ListIndex > -1 And ComboBox2.ListIndex > -1 And ComboBox3.ListIndex > -1 Then
 
    Set sh = Sheets(ComboBox1.Value)                                        'sheet
    Set f = sh.Range("B2:AF2").Find(ComboBox2.Value, , xlValues, xlWhole)   '1 to 31
    If Not f Is Nothing Then
      col = f.Column
      Set f = sh.Range("A:A").Find(ComboBox3.Value, , xlValues, xlWhole)   'data in column A
      If Not f Is Nothing Then
        sh.Cells(f.Row, col).Value = "X"
      End If
    End If
  End If
End Sub

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor

It's incredible. Everything is working. Thank you very much for your help