Using Private sub _change or _selection Change

lhernandez

Active Member
Joined
May 22, 2006
Messages
282
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:
Code:
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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,911
Office Version
  1. 365
Platform
  1. Windows
What is UnitRange?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

without looking at the contents of your code...

you shouldn't change the syntax provided by Excel
Code:
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

Active Member
Joined
May 22, 2006
Messages
282
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,911
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

lhernandez

Active Member
Joined
May 22, 2006
Messages
282

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,111,596
Messages
5,541,154
Members
410,543
Latest member
ExcelGlenn
Top