CODEWITHSUNDEEP
excelvbavariablesalias
Edgar Nava
Edgar Nava

Reputation: 21

Can a VBA function in excel be assigned to always be tied to a Range?

I have simple code that clears out the "State" cell whenever the "Payroll Country" cell changes. For example if the user selects "USA" in A6 and then selects "Arizona" in X6, then maybe later for some reason they change their mind and want to pick "CAN" for the country, the state cell will clear out.

But if someone in the future decides to insert a column before the X column, it will obviously move my State column over. Is there a way to make the VBA smarter (or make me smarter) so that the function will be tied to the "State" column rather than the specific "X" column?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub 'CountLarge handles larger ranges...
    'check Target column and row...
    If Target.Column = 1 And Target.Row >= 6 Then
        With Target.EntireRow
        
        'State column
            .Columns("X").Value = ""
            
        
        End With
    End If
End Sub

Upvotes: 0

Views: 74

Answers (3)

Ike
Ike

Reputation: 13054

There is another solution to your question (I was influenced by the short discussion with ACCtionMan regarding the enum-stuff):

If you can insert a table (insert > table) then you can use the listobject. Among a lot of other advantages you can reference the column by its name.

I assume that the table is named "tblData"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lo As ListObject
Set lo = Me.ListObjects("tblData")

If Not Intersect(Target, lo.ListColumns("Payroll Country").DataBodyRange) Is Nothing Then
    'changed cell is in Payroll country column then
    'take the intersection of the targets row and the State column to change the value
    Intersect(Target.EntireRow, lo.ListColumns("State").DataBodyRange) = vbNullString
End If

End Sub

But I would prefer the following solution - because I like to have business logic in the event handlers.

If a collegue of you (or even you in 6 months) looks into the change-event code he/she will immediately understand what is happening here - without reading how it is done.

Option Explicit

Private m_loData As ListObject

Private Sub Worksheet_Change(ByVal target As Range)

'if target cells is not within loData we don't need to check the entry
If Intersect(target, loData.DataBodyRange) Is Nothing Then Exit Sub

If ColumnHeaderOfRange(target) = "Payroll Country" Then
    resetStateColumnToEmptyValue target
End If

End Sub


Private Sub resetStateColumnToEmptyValue(c As Range)
Intersect(c.EntireRow, loData.ListColumns("State").DataBodyRange) = vbNullString
End Sub

'this could go in a general module - then add listobject as parameter
Private Function ColumnHeaderOfRange(c As Range) As String
On Error Resume Next ' in case c is outside of listobject
    ColumnHeaderOfRange = Intersect(c.Cells(1, 1).EntireColumn, loData.HeaderRowRange)
On Error GoTo 0
End Function

'this could be public then you can access the table from outside the worksheet module
Private Function loData() As ListObject
If m_loData Is Nothing Then
    Set m_loData = Me.ListObjects("tblData")
End If
Set loData = m_loData
End Function

Upvotes: 0

Toddleson
Toddleson

Reputation: 4457

You can use a named range, or you can use .Find to determine where your State column currently is. Here is an example using .Find

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.CountLarge > 1 Then Exit Sub 'CountLarge handles larger ranges...
    'check Target column and row...
    If Target.Column = 1 And Target.Row >= 6 Then
        Dim StateCol As Long
        StateCol = Me.Range("1:5").Find("State", LookIn:=xlValues, LookAt:=xlPart).Column
        
        With Target.EntireRow
        
        'State column
            .Columns(StateCol).Value = ""
            
        
        End With
    End If
End Sub

If you were to use a named range instead, you can define StateCol using StateCol = Me.Range("NamedRange").Column, it would be a little bit faster, since it doesn't need to search the row each time the user changes a value.

Side Note: The search range for .Find is Rows 1 to 5, but you may want to restrict or expand that range based on how you expect the data to move.

Upvotes: 1

Ike
Ike

Reputation: 13054

I (always) define an enum for the columns, like this

Public enum col_TableXXX    'adjust to your needs
  col_ID = 1
  col_PayrollCountry
  col_State
end enum

enums are numbered automatically - so col_PayrollCountry equals to 2, col_State equals to 3 etc.

In case there are new columns or the order changes you only have to move the enums around or add a new enum.

(You can avoid code typing by transpose-pasting the column titels on an excel sheet and then create the code via formulas)

You can then use the enums like this:

If target.column = col_PayrollCountry then
   target.entireRow.columns(col_State) = vbnullstring
End If

This is also much more "readable" than columns("X")

Culprit of this solution: you have to know that the columns changed. It is not an automatism that is based on the columns name.

Upvotes: 0

Related Questions