CODEWITHSUNDEEP
vbaexcel
AsZ
AsZ

Reputation: 11

VBA is not updating automatically after input

I have created a Excel workbook for my work where I collect information. In this workbook I have a sheet where details in various currencies can be filled in. Based on the selection by the preparer the value in cell B5 will change to either USD or LC. In case the value in cell B5 will be USD, columns C and E should be hidden. The issue in here is that this code will not immediately unhide the columns. After clicking on a random cell, the columns are hidden. Please let me know if there is a solution for this issue whereby the columns are hidden without clicking on a random cell each time. Thank you. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B5").Value = "USD" Then
        Union(Columns("C"), Columns("E")).EntireColumn.Hidden = True
    ElseIf Range("B5").Value = "LC" Then
        Union(Columns("C"), Columns("E")).EntireColumn.Hidden = False
    End If
End Sub

Thank you all for your comments. Let me further elaborate on my issue. In principle my VBA code works. The only thing is that after cell B5 is changed to USD, initially nothing happens. After I click on a random cell in this sheet, the VBA code works and hide the columns. The same is applicable in case the value in cell B5 is LC. Then again the VBA code does not work immediately. After clicking on a random cell in the sheet the columns are unhided.

A small update with respect to cell B5. So cell B5 contains a formula that is linked to listed cell in another sheet. After a value is selected from the list in another sheet, cell B5 will determine through the IF functions if the value in B5 will be LC or USD.

I am now afraid that after the preparer select in the listed cell a value, it will not click on a random cell in designated sheet resulting that he or she will see the wrong information.

Please let me know if you require further information. Thank you.

PS. I am not very strong in creating VBA codes.

Upvotes: 0

Views: 1459

Answers (3)

Davesexcel
Davesexcel

Reputation: 6984

Keep it simple,

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("B5")
    If rng.Value = "USD" Then Columns("C:E").EntireColumn.Hidden = True
    If rng.Value = "LC" Then Columns("C:E").EntireColumn.Hidden = False

    End Sub

Upvotes: 0

ashleedawg
ashleedawg

Reputation: 21619

You're using the wrong event.

SelectionChange fires when you select a different cell, etc.

Change fires immediately after a cell's contents change.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("B5").Value = "USD" Then
        Union(Columns("C"), Columns("E")).EntireColumn.Hidden = True
    ElseIf Range("B5").Value = "LC" Then
        Union(Columns("C"), Columns("E")).EntireColumn.Hidden = False
    End If
End Sub

Alternate Solution:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("B5") Then Union(Columns("C"), Columns("E")).EntireColumn.Hidden = (Target.Value = "USD")
End Sub

(adapted from @Vityata's comment below)

More Information:

Upvotes: 1

Matteo NNZ
Matteo NNZ

Reputation: 12645

A few changes to your code: 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$5" Then
        If Range("B5").Value = "USD" Then
            Union(Columns("C"), Columns("E")).EntireColumn.Hidden = True
        Else
            Union(Columns("C"), Columns("E")).EntireColumn.Hidden = False
        End If
        Application.Calculate
    End If
End Sub

1: the right event is Change (when a value changes in the sheet), not SelectionChange (when the selection changes).

2: Your code works on my side, so I guess it's not working on your side because you have some calculation off. I've added the line Application.Calculate which will refresh the spreadsheet even if your calculations are set to manual.

3: I've added an If Target.Address condition to check only when the cell that changed is the right one.

4: I've added just Else instead of ElseIf cell = "LC", because it's faster and because I think it's cleaner (if the user removes completely the value of the currency and before it was USD, nobody will unhide the columns C and E).

Upvotes: 0

Related Questions