Using Private sub _change or _selection Change

[lhernandez]

Ok I have a little problem setting the private sub up. I have a check box on my worksheet1 and when it is true I would like to develop a description on sheet2 in millimeters. When it is false I would like to develop a description in inches on sheet2.
Here is the code I have so far:

Private Sub UnitRate_Change(ByVal Target As Range)

Dim i As Long
i=2
    While Trim(sheet2.Cells(i, 1).Value) <> ""
        If Trim(sheet1.Cells(15, 3).Value) = True Then
            Trim(sheet2.Cells(i, 2).Value) = Trim(sheet2.Cells(i, 7) & " " & sheet2.Cells(i, 5) & " " & sheet2.Cells(i, 4))
        Else
            Trim(sheet2.Cells(i, 2).Value) = Trim(sheet2.Cells(i, 7) & " " & sheet2.Cells(i, 5) & " " & sheet2.Cells(i, 3))
        End If
    i = i + 1
    Wend
End Sub

Private Sub UnitRate_SelectionChange(ByVal Target As Range)

End Sub

but this does not work. can someone please lead me in the right direction.

 

[Norie]

What is UnitRange?

 

[erik.van.geit]

Hi,

without looking at the contents of your code...

you shouldn't change the syntax provided by Excel

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

just put the code in the correct sheetmodule and it will work

kind regards,
Erik

 

[lhernandez]

I already have a
private sub worksheet_change(ByVal Target as Range)
end sub
in the worksheet it needs to be in. I have just tried to add the code into the same module and i get error: Object Required.

 

[Norie]

You can't have more than one Change/SelectionChange event sub in a worksheet module.

What is that code actually meant to do?

Are you sure it should be event code?

I don't see you referring anywhere in the code to Target.

 

[erik.van.geit]

you would rather need to assign the code to your checkbox

 

[lhernandez]

Private worksheet_Change

Well I have a checkbox on sheet1 to choose between inches or meters. and sheet2 is a database of discriptions broken up by different catagories(i.e. size, color, type). the whole point of the workbook is the user can can use a "special description" on sheet1 and run a macro that pulls a large amount of information from sheet2 that is associated with the "Special description". But the special description cannot be found unless it is derived by what option (inches/meters) is chosen. In stead of having to manually run a macro after the checkbox is checked, i would like the macro to run if the checkbox changes from true to fals automatically.

 

[erik.van.geit]

i would like the macro to run if the checkbox changes from true to fals automatically

yes that's what my reply said

you would rather need to assign the code to your checkbox

if any problems assinging the macro, tell us what toolbar you used to create the checkbox (I presume it's an ActiveXcontrol)

 

[lhernandez]

Assigning Code to the Checkbox

How do I assign the code to the checkbox?

 

[erik.van.geit]

Re: Assigning Code to the Checkbox

How do I assign the code to the checkbox?

if any problems assinging the macro, tell us what toolbar you used to create the checkbox (I presume it's an ActiveXcontrol)