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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
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
76,303
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)
 

Forum statistics

Threads
1,141,155
Messages
5,704,626
Members
421,360
Latest member
Rhodia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top