After deselecting cell event

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Is there an event when a user deselects a cell?

The problem I have is a cell has data validation. After selecting a value, I have code that auto sizes the column width.

However, that only happens AFTER a value has been selected.

I want to autosize the moment the user selects from the list of possible values.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Once the user selects from the list of possible values, a value has been selected. I'm not seeing any difference between what it is already doing and what you actually want.
I understand your confusion. I haven't explained it very clearly.

Say the value in the cell of column 5 is Apples. The width is say 10. When the user clicks on the drop down, the list shows:

Pineapple
Very Ripe Pineapple
Extremely Tasty Grapefruit

Since the width is currently set at 10, the user cannot see the long options.

So I have written code to make the width wider, say 20. However if the user selects Orange, it will be fixed at 20.

Therefore I was wondering if there is an event that activates when the user clicks off a cell.

I have a workaround:

Code:
If Me.Columns <> 5 Then Me.Columns.Autofit
 
Upvote 0
When I read your first post, I thought that you were using Worksheet_Change, although it now appears that you are using Worksheet_SelectionChange.

Best way would be to use what you have to make the column wider when it is selected, then use Worksheet_Change to autofit the column when a selection is made from the dropdown. That way it will work even if the user doesn't move away from the selection.

Something like
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then Target.EntireColumn.AutoFit
End Sub
 
Upvote 0
Solution
When I read your first post, I thought that you were using Worksheet_Change, although it now appears that you are using Worksheet_SelectionChange.

Best way would be to use what you have to make the column wider when it is selected, then use Worksheet_Change to autofit the column when a selection is made from the dropdown. That way it will work even if the user doesn't move away from the selection.

Something like
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then Target.EntireColumn.AutoFit
End Sub
Thanks
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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