Help w/ CheckBox ?????

N

nrage21

I need help with a checkbox. I have a form with comboboxes, textboxe
and "one" checkbox. I need the checkbox when checked to highligth th
offset cells with a bright yellow color "colorindex = 6". Any hel
would be appreaciated.

The following is "part" of my code in the form.

Private Sub CommandButton1_Click()
'finds time string in column d
Dim FindString As String
Dim Rng As Range

FindString = ComboBox1.Value
If Trim(FindString) <> "" Then
Set Rng = Range("d:d").Find(what:=FindString, _
after:=Range("D" & Rows.Count), _
LookIn:=xlFormulas, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


'If cell next to time string is filled offset is disabled

If Rng.Offset(0, 1).Value = "" Then

'inserts content of text/combobx in sequence based on time strin
location
Rng.Offset(0, 2).Value = TextBox1.Text
Rng.Offset(0, 3).Value = TextBox2.Text
Rng.Offset(0, 6).Value = TextBox3.Text
Rng.Offset(0, 7).Value = ComboBox5.Text
Rng.Offset(0, 8).Value = ComboBox6.Text
Rng.Offset(0, 1).Value = TextBox6.Text
Rng.Offset(0, 0).Value = ComboBox1.Text
Rng.Offset(0, 4).Value = ComboBox3.Text
Rng.Offset(0, 5).Value = ComboBox2.Text
Rng.Offset(0, 9).Value = ComboBox4.Text
'Code for highlighting designated range'
If CheckBx1.Value = True Then
Offset range
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Else
offset range.Interior.ColorIndex = xlNone
End If


'As info is added message box pops up
MsgBox "Pickup Added Successfully"

response = MsgBox("Schedule another Pickup?", _
vbYesNo)
'When record is added code clears userform
If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
ComboBox5.Text = ""

TextBox6.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""
ComboBox4.Text = ""

ComboBox2.SetFocus
'when "no" is selected it closes form
Else
Unload Me
End If
Else
MsgBox "Time Slot Occupied"
End If

If Not Rng Is Nothing Then Application.Goto Rng, True
End If
End Sub

'cancels userform
Private Sub CommandButton2_Click()
End
End Sub


- Larry -
VBA Amateu
 
J

Jean-Yves

Hello,

If CheckBx1.Value = True Then
Rng.Offset(0, 1).resize(1,8).interior.colorindex= 6
....

Hope this is what you mean.
Regards,

Jean-Yves
 
N

nrage21

I get run time error '424':
Object required

I don't know if your suggestion would help me. My goal is that when th
user selects checkbox1 then it highlights (in yellow) the cells wher
text was placed by the offset procedure.

I don't know if I make myself clear.??

My code now:

Private Sub CommandButton1_Click()
'finds time string in column d
Dim FindString As String
Dim Rng As Range

FindString = ComboBox1.Value
If Trim(FindString) <> "" Then
Set Rng = Range("d:d").Find(what:=FindString, _
after:=Range("D" & Rows.Count), _
LookIn:=xlFormulas, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


'If cell next to time string is filled offset is disabled

If Rng.Offset(0, 1).Value = "" Then

'inserts content of text/combobx in sequence based on time strin
location
Rng.Offset(0, 2).Value = TextBox1.Text
Rng.Offset(0, 3).Value = TextBox2.Text
Rng.Offset(0, 6).Value = TextBox3.Text
Rng.Offset(0, 7).Value = ComboBox5.Text
Rng.Offset(0, 8).Value = ComboBox6.Text
Rng.Offset(0, 1).Value = TextBox6.Text
Rng.Offset(0, 0).Value = ComboBox1.Text
Rng.Offset(0, 4).Value = ComboBox3.Text
Rng.Offset(0, 5).Value = ComboBox2.Text
Rng.Offset(0, 9).Value = ComboBox4.Text
'Code for highlighting designated range'

If CheckBx1.Value = True Then
Rng.Offset(0, 1).Resize(1, 8).Interior.ColorIndex = 6
Else
Rng.Offset(0, 1).Resize(1, 8).Interior.ColorIndex
xlNone
End If


'As info is added message box pops up
MsgBox "Pickup Added Successfully"

response = MsgBox("Schedule another Pickup?", _
vbYesNo)
'When record is added code clears userform
If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
ComboBox5.Text = ""

TextBox6.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""
ComboBox4.Text = ""

ComboBox2.SetFocus
'when "no" is selected it closes form
Else
Unload Me
End If
Else
MsgBox "Time Slot Occupied"
End If

If Not Rng Is Nothing Then Application.Goto Rng, True
End If
End Sub

'cancels userform
Private Sub CommandButton2_Click()
End
End Sub


- Larry -
VBA Amateu
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code does not error but no copy either 4
adding 1 hour to input box 11
macro OK in Excel 2003 but NOT in Excel 2000 4
Error 1004 in search/copy 3
Type Mismatch 5
for next loop question 3
Find with criteria 4
Changing number of input boxes??? 1

Top