Snasui Excel Forum

Code: Select all

If cbomonth.Value = "January" Then
    Range("a6").Select

Code: Select all

Private Sub cboroom_Change()
myroom = cboroom.Text
If cboroom.Value = "2A" Then  'อ้างถึงชื่อห้อง
    Sheets("Jan").Select
    Range("b6").Select
    Do While True
    If myroom = ActiveCell.Value Then
   
        TextBox1.Text = ActiveCell.Offset(0, i + 1).Value
        TextBox6.Text = ActiveCell.Offset(0, i + 2).Value
        TextBox2.Text = ActiveCell.Offset(0, i + 3).Value
        TextBox7.Text = ActiveCell.Offset(0, i + 4).Value
        TextBox4.Text = ActiveCell.Offset(0, i + 5).Value
        TextBox8.Text = ActiveCell.Offset(0, i + 6).Value
        TextBox9.Text = ActiveCell.Offset(0, i + 7).Value
        TextBox10.Text = ActiveCell.Offset(0, i + 8).Value
        TextBox12.Text = ActiveCell.Offset(0, i + 9).Value
        TextBox11.Text = ActiveCell.Offset(0, i + 10).Value
        TextBox13.Text = ActiveCell.Offset(0, i + 11).Value
        Exit Do
    End If
    ActiveCell.Offset(1, 0).Select
   Loop
End If
End Sub

Code: Select all

Private Sub cboroom_Change()
myroom = cboroom.Text
If cbomonth.Value = "January" And cboroom.Value = "2A" Then
    Sheets("Jan").Select
    Range("b6").Select
    Do While True
    If myroom = ActiveCell.Value Then
   
        TextBox1.Text = ActiveCell.Offset(0, i + 1).Value
        TextBox6.Text = ActiveCell.Offset(0, i + 2).Value
        TextBox2.Text = ActiveCell.Offset(0, i + 3).Value
        TextBox7.Text = ActiveCell.Offset(0, i + 4).Value
        TextBox4.Text = ActiveCell.Offset(0, i + 5).Value
        TextBox8.Text = ActiveCell.Offset(0, i + 6).Value
        TextBox9.Text = ActiveCell.Offset(0, i + 7).Value
        TextBox10.Text = ActiveCell.Offset(0, i + 8).Value
        TextBox12.Text = ActiveCell.Offset(0, i + 9).Value
        TextBox11.Text = ActiveCell.Offset(0, i + 10).Value
        TextBox13.Text = ActiveCell.Offset(0, i + 11).Value
        Exit Do
    End If
    ActiveCell.Offset(1, 0).Select
   Loop
ElseIf cbomonth.Value = "January" And cboroom.Value = "2B" Then
    Sheets("Jan").Select
    Range("b6").Select
    Do While True
    If myroom = ActiveCell.Value Then
   
        TextBox1.Text = ActiveCell.Offset(0, i + 1).Value
        TextBox6.Text = ActiveCell.Offset(0, i + 2).Value
        TextBox2.Text = ActiveCell.Offset(0, i + 3).Value
        TextBox7.Text = ActiveCell.Offset(0, i + 4).Value
        TextBox4.Text = ActiveCell.Offset(0, i + 5).Value
        TextBox8.Text = ActiveCell.Offset(0, i + 6).Value
        TextBox9.Text = ActiveCell.Offset(0, i + 7).Value
        TextBox10.Text = ActiveCell.Offset(0, i + 8).Value
        TextBox12.Text = ActiveCell.Offset(0, i + 9).Value
        TextBox11.Text = ActiveCell.Offset(0, i + 10).Value
        TextBox13.Text = ActiveCell.Offset(0, i + 11).Value
        Exit Do
    End If
    ActiveCell.Offset(1, 0).Select
   Loop
   ElseIf cbomonth.Value = "January" And cboroom.Value = "2C" Then
    Sheets("Jan").Select
    Range("b6").Select
    Do While True
    If myroom = ActiveCell.Value Then
   
        TextBox1.Text = ActiveCell.Offset(0, i + 1).Value
        TextBox6.Text = ActiveCell.Offset(0, i + 2).Value
        TextBox2.Text = ActiveCell.Offset(0, i + 3).Value
        TextBox7.Text = ActiveCell.Offset(0, i + 4).Value
        TextBox4.Text = ActiveCell.Offset(0, i + 5).Value
        TextBox8.Text = ActiveCell.Offset(0, i + 6).Value
        TextBox9.Text = ActiveCell.Offset(0, i + 7).Value
        TextBox10.Text = ActiveCell.Offset(0, i + 8).Value
        TextBox12.Text = ActiveCell.Offset(0, i + 9).Value
        TextBox11.Text = ActiveCell.Offset(0, i + 10).Value
        TextBox13.Text = ActiveCell.Offset(0, i + 11).Value
        Exit Do
    End If
    ActiveCell.Offset(1, 0).Select
   Loop
   
End If
End Sub

Code: Select all

Private Sub cboroom_Change()
    myroom = cboroom.Text
    'If cboroom.Value = "2A" Then
    On Error Resume Next
    Sheets(Left(cbomonth.Text, 3)).Select
    If Err > 0 Then Exit Sub
    Range("b6").Select
    Do While ActiveCell <> ""
        If myroom = ActiveCell.Value Then
            TextBox1.Text = ActiveCell.Offset(0, 1).Value
            TextBox6.Text = ActiveCell.Offset(0, 2).Value
            TextBox2.Text = ActiveCell.Offset(0, 3).Value
            TextBox7.Text = ActiveCell.Offset(0, 4).Value
            TextBox4.Text = ActiveCell.Offset(0, 5).Value
            TextBox8.Text = ActiveCell.Offset(0, 6).Value
            TextBox9.Text = ActiveCell.Offset(0, 7).Value
            TextBox10.Text = ActiveCell.Offset(0, 8).Value
            TextBox12.Text = ActiveCell.Offset(0, 9).Value
            TextBox11.Text = ActiveCell.Offset(0, 10).Value
            TextBox13.Text = ActiveCell.Offset(0, 11).Value
            Exit Do
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
    'End If
End Sub

Code: Select all

Private Sub cbomonth_AfterUpdate()
            Dim shTemp As Worksheet
            Set shTemp = Worksheets("shTemp")
            shTemp.Range("B1") = Null
            shTemp.Range("A1") = Me.cbomonth
End Sub

Code: Select all

Private Sub cboroom_Change()
            Dim shTemp As Worksheet
            Set shTemp = Worksheets("shTemp")
            
            If IsNull(Me.cbomonth) Then
                    MsgBox "Please Input Month", vbCritical
            Else
                    shTemp.Range("B1") = Me.cboroom
                    Me.TextBox1 = shTemp.Range("A3")
                    Me.TextBox6 = shTemp.Range("B3")
                    Me.TextBox2 = shTemp.Range("C3")
                    Me.TextBox7 = shTemp.Range("D3")
                    Me.TextBox4 = shTemp.Range("E3")
                    Me.TextBox8 = shTemp.Range("F3")
                    Me.TextBox9 = shTemp.Range("G3")
                    Me.TextBox10 = shTemp.Range("H3")
                    Me.TextBox12 = shTemp.Range("I3")
                    Me.TextBox11 = shTemp.Range("J3")
                    Me.TextBox13 = shTemp.Range("K3")

            End If
End Sub