Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help again
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help again
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help again
I did what you suggested and all the variables had the right value except
'lnknum'. It had a value of 0. "Joel" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help again
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help again
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. "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|