Check Box

[zak]

Hi

Please see my code below. I would like to know if anyone can help me to add
a condition in the code.

The condition is basically that I have a check box on a form, and if the box
is check (with a tick obviously) then I want the word "Yes" to be cell G for
the corresponding row. Is there any way I can add some code to the below to
do this?

Private Sub SendAppReq_Click()

If Sheets("Appointments").Range("A3").Value = "" _
Then
R = 3
Else
R = Sheets("Appointments").Range("A3").End(xlDown).Row + 1
End If

With Sheets("Appointments")
.Range("A" & R).Value = BookApp.OwnerN.Text
.Range("B" & R).Value = BookApp.AppDur.Text
.Range("C" & R).Value = BookApp.LaptopDetails.Text
.Range("D" & R).Value = BookApp.PropDate.Text
.Range("E" & R).Value = BookApp.PropTime.Text
.Range("F" & R).Value = BookApp.AppDur.Text
Unload BookApp
BookApp.Hide
End With
Unload BookApp
End Sub

Thanking you in advance

 

[Dave Peterson]

Maybe...

Private Sub SendAppReq_Click()

If Sheets("Appointments").Range("A3").Value = "" _
Then
R = 3
Else
R = Sheets("Appointments").Range("A3").End(xlDown).Row + 1
End If

With Sheets("Appointments")
.Range("A" & R).Value = Me.OwnerN.Text
.Range("A" & R).Value = Me.OwnerN.Text
.Range("B" & R).Value = Me.AppDur.Text
.Range("C" & R).Value = Me.LaptopDetails.Text
.Range("D" & R).Value = Me.PropDate.Text
.Range("E" & R).Value = Me.PropTime.Text
.Range("F" & R).Value = Me.AppDur.Text
if me.checkbox1.value = true then
.Range("G" & R).Value = "yes"
else
.Range("G" & R).Value = "" '"no" '???
end if
End With
Unload Me
End Sub

Me refers to the object that owns the code--in this case, I'm guessing it's the
BookApp userform.

 

[zak]

Hi

Thanks for getting back to me, when i try your code i get an error (yellow
highlight) on the .Range("A" & R).Value = Me.OwnerN.Text line.

Any ideas??

Thanks again.

 

[Dave Peterson]

I changed BookApp to Me. Because I thought you were populating the cell with
the value from the control on that same userform as the commandbutton.

If that's not true, then change it back to BookApp.

Maybe add a line to help you debug:

msgbox R & vblf & me.ownern.text
or
msgbox R & vblf & bookapp.ownern.text

to make sure that the variables are what you expected.

 

[zak]

Hi Dave

Your correct, I am populating the cell with the value from the control box
which is on the same userform.

Shall I still try the msgBox code below?

 

[Dave Peterson]

Yes.

And make sure that Appointments is not protected.

Hi Dave

Your correct, I am populating the cell with the value from the control box
which is on the same userform.

Shall I still try the msgBox code below?

 

[zak]

Hi Dave

Where in the code shall i put - msgbox R & vblf & bookapp.ownern.text?

Thanks

 

[zak]

Dave

sorry to bother you again, but its not working again, but i know what the
problem is now.

It adds it the first time, but if i opened the form again to add another
appointment it falls over and does the yellow highlight thing, so its not
putting the info into the next row. i.e. it puts it into row A3, but falls
over and doesnt put it onto row B3 and so on

Is there anyway I could overcome this problem??

Thank you.

 

[Dave Peterson]

This portion of your code:

Should be figuring out what row gets the data. You still have that portion in
your code, right?

If you don't, then add it back.

If you do, then add another line right after that portion of code:

msgbox R & " - This row gets the data!"

.... rest of your code.

If that doesn't work, you'll want to post the code you're using.

Dave

sorry to bother you again, but its not working again, but i know what the
problem is now.

It adds it the first time, but if i opened the form again to add another
appointment it falls over and does the yellow highlight thing, so its not
putting the info into the next row. i.e. it puts it into row A3, but falls
over and doesnt put it onto row B3 and so on

Is there anyway I could overcome this problem??

Thank you.

 

[zak]

Thanks Dave

I have tried the message code and figured out what the problem was and now
it works.

Thanks for all of your help with this.