Worksheet macro works on one sheet, but won't work on another

D

dilligaf5298

New Member
Joined
Sep 19, 2012
Messages
4
Group, this is my first time posing a question, so please forgive my ineptitude.

I have workbook that has 43 sheets and it's used to calculate accident reconstruction formulas. Sheet 1 is for Coefficient of Friction; Sheet 18 is for Speed Change in a Collision; etc. My goal is to be able to enter a value for a needed variable in imperial in a cell, but have that same value converted to its metric equivalent in a companion cell automatically. If i enter the metric value, the imperial equivalent is automatically calculated and displayed.

I've written a macro designed to be used only on the active worksheet that is listed as "Selection Change", as the entry cells are not the same on each sheet. For instance, it includes:

With ActiveSheet
If Target = .[F4] Then
​
.[G4].Value = .[F4].Value * 2.2046
ElseIf Target = .[G4] Then
.[F4].Value = .[G4]. Value / 2.2046​
End If
​
End With

Depending on the number of variables for that sheet, there are more iterations of the above, but tailored to address whatever cell value is used (the example shown converts weight from pounds to kilograms).

This macro will work on Sheet 18 without problem. When copied to Sheet 1 module (or others) with the cell addresses and conversion values corrected, it won't work. On each sheet module, the macro is classified as "Worksheet" and "SelectionChange". I'm using Office 365 (latest) and Windows 10. The name listed for each module window in VBA is "Sheet 1 [or as appropriate] (Code)" and the initial lines of the macro are "Private Sub Worksheet_SelectionChange (ByVal Target As Range)" followed by "Application.EnableEvents = False" on the second line. The EnableEvents returns to "True" immediately before the "End Sub"

I'll be glad to paste the macro in if I can figure out how to do that correctly.

Thank you in advance for your tolerance, help, and most importantly, all the help I've gleaned from this forum over the years.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
I don't understand why you use a selectionchange_event to trigger the value conversion to other units. Why not use a change_event so when the user enters a new value in F4 its then converted to the preferred units in G4?
 
Upvote 0
Joe, I had it set up as a change_event, but it didn't work on any sheet. After i changed the one I happened to be on - sheet 18 - it began working as intended. I took the position that, if it works that way on THAT sheet, why won't it do so on another. Shows what I know.

I'm open to any suggestions. I probably know just enough about macros to be really dangerous.

Dave
 
Upvote 0
I also believe you need a Change event not a selection change event

I would also like to see your entire script.

And Why F4 and G4

And I believe you said this is just a part of the script you want.
You said:

Depending on the number of variables for that sheet, there are more iterations of the above, but tailored to address whatever cell value is used (the example shown converts weight from pounds to kilograms).

If you were to provide all the different
iterations we maybe could write you one script that would work for all sheets.

​
​
 
Upvote 0
This is typical of the macro for one sheet (Sheet 12). Each sheet has differing variables and the input cells aren't all in the same place, but the general format is as follows:

Code: 
[SIZE=1]Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    
    With ActiveSheet
        'Middle ordinate conversions
        If Target = .[f8] Then
            .[g8].Value = .[f8].Value * 0.3048
        ElseIf Target = .[g8] Then
            .[f8].Value = .[g8].Value / 0.3048
        End If
    End With
    
    With ActiveSheet
        'Radius conversions
        If Target = .[f10] Then
            .[g10].Value = .[f10].Value * 0.3048
        ElseIf Target = .[g10] Then
            .[f10].Value = .[g10].Value / 0.3048
        End If
    End With
    
    With ActiveSheet
       'Chord conversions
       If Target = .[f20] Then
            .[g20].Value = .[f20].Value * 0.3048
        ElseIf Target = .[g20] Then
            .[f20].Value = .[g20].Value / 0.3048
        End If
    End With
    
    With ActiveSheet
        'Lateral Distance conversions
        If Target = .[f22] Then
            .[g22].Value = .[f22].Value * 0.3048
        ElseIf Target = .[g22] Then
            .[f22].Value = .[g22].Value / 0.3048
        End If
    End With
    
    With ActiveSheet
        'Speed conversions
        If Target = .[m4] Then
            .[n4].Value = .[m4].Value * 1.6093
        ElseIf Target = .[n4] Then
            .[m4].Value = .[n4].Value / 1.6093
        End If
    End With
    
    With ActiveSheet
        'Speed initial conversions
        If Target = .[m6] Then
            .[n6].Value = .[m6].Value * 1.6093
        ElseIf Target = .[n6] Then
            .[m6].Value = .[n6].Value / 1.6093
        End If
    End With
    
    With ActiveSheet
        'Speed final conversions
        If Target = .[m8] Then
            .[n8].Value = .[m8].Value * 1.6093
        ElseIf Target = .[n8] Then
            .[m8].Value = .[n8].Value / 1.6093
        End If
    End With
    
    With ActiveSheet
        'Velocity conversions
        If Target = .[m10] Then
            .[n10].Value = .[m10].Value * 0.3048
        ElseIf Target = .[n10] Then
            .[m10].Value = .[n10].Value / 0.3048
        End If
    End With
    
    With ActiveSheet
        'Velocity initial conversions
        If Target = .[m12] Then
            .[n12].Value = .[m12].Value * 0.3048
        ElseIf Target = .[n12] Then
            .[m12].Value = .[n12].Value / 0.3048
        End If
    End With
    
    With ActiveSheet
        'Velocity final conversions
        If Target = .[m14] Then
            .[n14].Value = .[m14].Value * 0.3048
        ElseIf Target = .[n10] Then
            .[m14].Value = .[n14].Value / 0.3048
        End If
    End With
    
    With ActiveSheet
        'Acceleration conversions
        If Target = .[m16] Then
            .[n16].Value = .[m16].Value * 3.2808
        ElseIf Target = .[n16] Then
            .[m16].Value = .[n16].Value / 3.2808
        End If
    End With
    
    With ActiveSheet
        'Weight conversions
        If Target = .[m18] Then
            .[n18].Value = .[m18].Value * 2.2046
        ElseIf Target = .[n18] Then
            .[m18].Value = .[n18].Value / 2.2046
        End If
    End With
    
    With ActiveSheet
        'Kinetic energy conversions
        If Target = .[m20] Then
            .[n20].Value = .[m20].Value * 1.3558
        ElseIf Target = .[n20] Then
            .[m20].Value = .[n20].Value / 1.3558
        End If
    End With
        
    Application.EnableEvents = True
    
End Sub[/SIZE]
Again, I thank you for the help.
 
Last edited by a moderator:
Upvote 0
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 
Upvote 0
So your saying this doesn't work on more than one sheet ???
Did you, at some point, stop the code before it had finished ???
If so, I'd suggest enableevents is still turned off !!
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

With ActiveSheet
'Middle ordinate conversions
If Target = .[f8] Then
.[g8].Value = .[f8].Value * 0.3048
ElseIf Target = .[g8] Then
.[f8].Value = .[g8].Value / 0.3048
End If

'Radius conversions
If Target = .[f10] Then
.[g10].Value = .[f10].Value * 0.3048
ElseIf Target = .[g10] Then
.[f10].Value = .[g10].Value / 0.3048
End If

'Chord conversions
If Target = .[f20] Then
.[g20].Value = .[f20].Value * 0.3048
ElseIf Target = .[g20] Then
.[f20].Value = .[g20].Value / 0.3048
End If

'Lateral Distance conversions
If Target = .[f22] Then
.[g22].Value = .[f22].Value * 0.3048
ElseIf Target = .[g22] Then
.[f22].Value = .[g22].Value / 0.3048
End If

'Speed conversions
If Target = .[m4] Then
.[n4].Value = .[m4].Value * 1.6093
ElseIf Target = .[n4] Then
.[m4].Value = .[n4].Value / 1.6093
End If

'Speed initial conversions
If Target = .[m6] Then
.[n6].Value = .[m6].Value * 1.6093
ElseIf Target = .[n6] Then
.[m6].Value = .[n6].Value / 1.6093
End If

'Speed final conversions
If Target = .[m8] Then
.[n8].Value = .[m8].Value * 1.6093
ElseIf Target = .[n8] Then
.[m8].Value = .[n8].Value / 1.6093
End If

'Velocity conversions
If Target = .[m10] Then
.[n10].Value = .[m10].Value * 0.3048
ElseIf Target = .[n10] Then
.[m10].Value = .[n10].Value / 0.3048
End If

'Velocity initial conversions
If Target = .[m12] Then
.[n12].Value = .[m12].Value * 0.3048
ElseIf Target = .[n12] Then
.[m12].Value = .[n12].Value / 0.3048
End If

'Velocity final conversions
If Target = .[m14] Then
.[n14].Value = .[m14].Value * 0.3048
ElseIf Target = .[n10] Then
.[m14].Value = .[n14].Value / 0.3048
End If

'Acceleration conversions
If Target = .[m16] Then
.[n16].Value = .[m16].Value * 3.2808
ElseIf Target = .[n16] Then
.[m16].Value = .[n16].Value / 3.2808
End If

'Weight conversions
If Target = .[m18] Then
.[n18].Value = .[m18].Value * 2.2046
ElseIf Target = .[n18] Then
.[m18].Value = .[n18].Value / 2.2046
End If

'Kinetic energy conversions
If Target = .[m20] Then
.[n20].Value = .[m20].Value * 1.3558
ElseIf Target = .[n20] Then
.[m20].Value = .[n20].Value / 1.3558
End If
End With

Application.EnableEvents = True

End Sub
 
Upvote 0
forgot to add, to enable events...Press CTRL + G to open the Immediates window
Paste this into that window
Code: 
Application.EnableEvents = True
Place the cursor anywhere within that text and press Enter.
See if that helps
 
Upvote 0
And that, ladies and gentlemen, fixed the problem.

Thank you from the bottom of my heart Michael and all who took time to review this.

Dave
 
Upvote 0
Glad to help....and thx for the feedback..(y)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
511
kevin9999
K
G
  • Solved
Multiple Macros on one sheet not working
Replies
3
Views
368
GATA_Spread
G
T
  • Solved
Creating an automated macro that changes one cell value if another cell value is equal or less than a specific number
2 3 4
Replies
30
Views
1K
kevin9999
K
S
  • Question
Run a Macro based on the Value of a Cell in a Worksheet
Replies
3
Views
708
rollis13
rollis13
S
  • Solved
Macro to get a cell copied from one worksheet to another, this is an odd situation
Replies
3
Views
346
sspatriots
S

Forum statistics

Threads
1,215,575
Messages
6,125,628
Members
449,240
Latest member
lynnfromHGT

Share this page

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
Back
Top