Call Macro from module in worksheet change event based on ListBox selection

[spgexcel]

Hi
I have a form control ListBox with some options in it. It is linked to A1 cell.
I have following code in sheet code. I want the autofit code triggered each time there is change in A1 cell

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then

Call autofit

End If

End Sub

I want to call Macro called "Autofit" from module which is as follows:

Sub autofit()

Worksheets("Launch Dates").Range("I8:I36").Columns.autofit

End Sub

But nothing happens when the cell value changes.
Am I doing anything wrong?

Thanks in advance!

Sumant

 

[aaewalsh]

The code you've provided should work as expected, assuming the macro "Autofit" and the code that calls it are in the correct location.​

Here's a few things you can check to troubleshoot:

• Make sure that the name of the macro is spelled correctly, as the code will not run if the macro cannot be found.
• Ensure that the sheet where the cell is located is active when a change occurs. If the sheet is not active, the Worksheet_Change event will not fire.
• Make sure that the range in the Autofit macro is correct, "Worksheets("Launch Dates").Range("I8:I36").Columns.autofit"
• Also check, if the sheet's name "Launch Dates" is correctly spelled
• Add some debugging code, like MsgBox("Change detected in A1!") inside the if condition in "Worksheet_Change" to see if the event is firing at all or not.

If you're still having trouble after checking these things, please let me know the error message or if no error message, the outcome you are getting.

 

[spgexcel]

The code you've provided should work as expected, assuming the macro "Autofit" and the code that calls it are in the correct location.​

Here's a few things you can check to troubleshoot:

• Make sure that the name of the macro is spelled correctly, as the code will not run if the macro cannot be found.
• Ensure that the sheet where the cell is located is active when a change occurs. If the sheet is not active, the Worksheet_Change event will not fire.
• Make sure that the range in the Autofit macro is correct, "Worksheets("Launch Dates").Range("I8:I36").Columns.autofit"
• Also check, if the sheet's name "Launch Dates" is correctly spelled
• Add some debugging code, like MsgBox("Change detected in A1!") inside the if condition in "Worksheet_Change" to see if the event is firing at all or not.

If you're still having trouble after checking these things, please let me know the error message or if no error message, the outcome you are getting.

Hi aaewalsh, I checked all points as you mentioned, All good.
In fact Autofit macro runs perfectly if I run it individually.
However, I added the Msgbox debugging code, it did nothing. No message box appears. Weird.
Here is that code I added:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then

MsgBox ("Change detected in A1")

Call Autofit

End If

End Sub

 

[aaewalsh]

Could you perhaps send the file I can review it and debug

 

[jolivanes]

This works for me.
In Sheet Module

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Call Fit_Auto
End If
End Sub

In regular module

Sub Fit_Auto()
    Worksheets("Launch Dates").Columns(9).Autofit
End Sub

Sorry. Just noticed that you work with a ListBox. Won't work.

 

[jolivanes]

Right click on the ListBox and choose "Asign Macro". Select "Fit_Auto"

Do away with the "Worksheet_Change" code.

 

[spgexcel]

Right click on the ListBox and choose "Asign Macro". Select "Fit_Auto"

Do away with the "Worksheet_Change" code.

Ah. Great this worked! Thanks a ton @jolivanes !! Have a great day ahead!

 

[jolivanes]

Thank you for the update and a great day to you also.
Good Luck