Thanks a lot Dave - you deliver as always !
I have applied your suggested code and it works perfectly. You're also quite
right that the code is placed in a worksheet, so I'm being careful to use the
correct reference.
I was not familiar with the Resize property - now I am and you're right
(again) that it is easier to work with.
The Doctor
"Dave Peterson" wrote:
> I think I'd drop going through the shapes collection and just pass the
> UpdateDataLine sub the checkbox and the number you want:
>
> Option Explicit
> Private Sub CheckBox1_Click()
> Call UpdateDataLine(CheckBox1, 1)
> End Sub
> Private Sub CheckBox2_Click()
> Call UpdateDataLine(CheckBox2, 2)
> End Sub
> Sub UpdateDataLine(CBX As MSForms.CheckBox, BoxId As Long)
> If CBX.Value = True Then
> With ThisWorkbook.Worksheets("mysheet")
> .Range(.Cells(BoxId + 2, 1), .Cells(BoxId + 2, 3)).Value _
> = .Range(.Cells(BoxId + 2, 15), .Cells(BoxId + 2, 17)).Value
> End With
> End If
> End Sub
>
> And be careful with your ranges.
>
> This kind of unqualified range (the cells() stuff) causes trouble:
>
> ThisWorkbook.Worksheets("mysheet").Range(Cells(BoxId + 2, 1), Cells(BoxId + 2,
> 3)).Value
>
> If this subroutine is in a general module, then the unqualified cells() will
> refer to the activesheet.
>
> But I'm betting that the sub is behind the worksheet. In that case, the
> unqualifed cells() refer to the sheet that owns the code. And that may not be
> MySheet.
>
> And sometimes, you can refer to ranges so that it's a little easier to read.
>
> With ThisWorkbook.Worksheets("mysheet")
> .Cells(BoxId + 2, 1).Resize(1, 3).Value _
> = .Cells(BoxId + 2, 15).Resize(1, 3).Value
> End With
>
>
>
>
> Dr. Schwartz wrote:
> >
> > In Excel 2003 I have 12 control checkboxes (only 2 below in the code) in my
> > sheet. Depending on the value of the individual checkbox a piece of code
> > should run.
> >
> > Public BoxId As Integer
> > Private Sub CheckBox1_Click()
> > BoxId = 1
> > UpdateDataLine
> > End Sub
> >
> > Private Sub CheckBox2_Click()
> > BoxId = 2
> > UpdateDataLine
> > End Sub
> > Sub UpdateDataLine()
> > 'BoxId = BoxId + 4145
> > Dim cbx As shape, cbx_val
> > For Each cbx In ActiveSheet.Shapes
> > If cbx.ID = BoxId + 4145 Then
> > If cbx.Value = True Then 'this one is causing me problems
> > ThisWorkbook.Worksheets("mysheet").Range(Cells(BoxId + 2,
> > 1), Cells(BoxId + 2, 3)).Value = _
> > ThisWorkbook.Worksheets("mysheet").Range(Cells(BoxId + 2,
> > 15), Cells(BoxId + 2, 17)).Value
> > End If
> > End If
> > Next
> > End Sub
> >
> > My problem is that I can't find a way to determine the value of the given
> > checkbox. when i type cbx. I don't get the Value option in the dropdown list
> > that appear so I'm sure I'm doing something fundamentally wrong.
> >
> > Is my code "the long way" to perform a simple thing or how can I make it work?
> >
> > Anyone?
> >
> > Thanks
> > The Doctor
>
> --
>
> Dave Peterson
>
|
Similar Threads
