dimanche 5 février 2017

Excel vba userform setting cell interior doesn't go to next row

I am trying to make a userform and I want it to change the color of the next cell. It works everytime when I just use strings but every time I try to change the cell interior, it stays on the same row instead of going 1 row down like it should. When I run it without the color part, everything works just fine and the strings go to the next row every time I want them to. But when I insert the cell color interior part it doesn't go 1 down. I want to make it that if the checkbox is checked, the first column of the row is in green and if it isn't checked, it's red.

Here's the code:

Private Sub btnEnter_Click()
Dim ssheet As Worksheet

If Me.tbTitle.Value = "" Or Me.tbDate.Value = "" Or Me.cmbGenre = "" Or Me.tbKeywords = "" Or Me.tbDirector = "" Or Me.tbCast = "" Then
    If MsgBox("Not all forms are completed. Do you want to continue?", vbQuestion + vbYesNo) <> vbYes Then
    Exit Sub
    End If
End If

Set ssheet = ThisWorkbook.Sheets("MovieList")

nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

If CheckBox1.Value = True Then
    ssheet.Cells(nr, 1).Interior.ColorIndex = 4
    Else: ssheet.Cells(nr, 1).Interior.ColorIndex = 3
End If

ssheet.Cells(nr, 2) = Me.tbTitle
ssheet.Cells(nr, 3) = Me.tbDate
ssheet.Cells(nr, 4) = Me.cmbGenre
ssheet.Cells(nr, 5) = Me.tbKeywords
ssheet.Cells(nr, 6) = Me.tbDirector
ssheet.Cells(nr, 7) = Me.tbCast
ssheet.Cells(nr, 8) = Me.tbComments

Call resetForm

End Sub

Sub resetForm()

Me.tbTitle = ""
Me.tbDate = ""
Me.cmbGenre = ""
Me.tbKeywords = ""
Me.tbDirector = ""
Me.tbCast = ""
Me.tbComments = ""
Me.tbTitle.SetFocus

End Sub

Private Sub CheckBox1_Click()

End Sub

Private Sub UserForm_Initialize()

'fill combobox
For Each cell In [ListGenre]
    Me.cmbGenre.AddItem
Next cell


End Sub

I would be so glad if you could help me. Thank you.

Aucun commentaire:

Enregistrer un commentaire