I need help again

[ranswrt]

I have problems with a worksheet with some buttons using the forms toolbar.
It was working fine with the buttons from the forms toolbar. I changed the
buttons to commandbutton from the control toolbar so I could make changes to
the properties. Since I made the change a lot of the code that was working
fine using the buttons doesn't run using the command button. With some help
I think the problem is that the ranges I am accessing are on a different
sheet than the command buttons are. The sheet that has the command buttons
is named 'Estimates'. The sheet that I am trying to access the named cell is
called 'Estimates DB". I am now having problems with the following procedure
that is called from another procedure which is called from a command button
on the 'Estimates' sheet.

Sub addcheckbox()
Dim a As String
Dim cboxname As Integer
Dim ycell As Range
Dim chk As Excel.CheckBox
Dim z As String
Dim num As Long
Dim estnum As Long
Dim est As String
Dim lnknum As Long

est = Range("currentestimate")
estnum = Range("estimatenumber")
num = Range("estnum")
With Sheets("Estimates DB")
Set ycell = .Range("estdblinkno")
lnknum = ycell.Offset(estnum, 0)
End With
MsgBox ("lnknum= " & lnknum)
Set ycell = Sheets("Estimates").Range("estno").Offset(num, 1)
Set chk = ycell.Parent.Checkboxes.Add(ycell.Left, ycell.Top, 0, ycell.Height)
chk.Height = ycell.Height - 1.5
chk.Characters.Text = ""
chk.Name = "Checkbox" & est & lnknum
z = Sheets("Estimates DB").Range("estdbcboxlnkcell").Offset(estnum, 0).address
z = "'Estimates DB'!" & z
chk.LinkedCell = z
chk.Visible = True
chk.Display3DShading = True
chk.Value = False
chk.OnAction = "updateestimatetotal"

End Sub

I keep getting 0 for the value for lnknum. I tried all the variations to
try and get the right value and I keep getting 0. Any ideas on why this
won't work?
Thanks

 

[Joel]

here are too many reasons why the code isn't working. I would set a break
point Using F9 at the first line

est = Range("currentestimate")

First click on the line with the mouse and then Press F9. Run the code like
you normally would do. the execution will stop on the line with the break
point. Then step though the code by pressing F8. You can see each of the
variable values by moving the mouse over the variables. The line that is
highlighted is the next instrution that will execute.

You can also add the variables to the wath window by highlighting the
variables then right click and select "Add to Watch".

 

[ranswrt]

I did what you suggested and all the variables had the right value except
'lnknum'. It had a value of 0.

 

[Rick Rothstein \(MVP - VB\)]

My guess is your problem is in this section...

est = Range("currentestimate")
estnum = Range("estimatenumber")
num = Range("estnum")
With Sheets("Estimates DB")
Set ycell = .Range("estdblinkno")
lnknum = ycell.Offset(estnum, 0)
End With

Without knowing what those named ranges are holding or their intended
interrelationship with each other, we can only guess at possible causes to
your problem. Since lnknum is not getting the value you expect, the problem
almost has to be with the Offset function call (you are offsetting a number
of cells other than what you think you should be offsetting). I see you are
using the 'estnum' variable for the Offset function call... should you
perhaps be using the 'num' variable instead (it is calculated from the cell
with range name "estnum")?

By the way, while you assign a value to the 'est' variable in the first line
above, you don't seem to use it in the rest of your code anywhere.

Rick

 

[ranswrt]

I found my problem. I was running the addcheckbox procedure before the value
was set in the cell lnknum was trying ro access. It was another easy
solution that I was look over the top of it.
Thanks for your help.