Need help with a VBA Check

[jetjaguar15]

I have the following Code setup.

' Don't allow changes in the column names or outside of the table borders
If Target.Row < 2 Or Sh.Cells(1, Target.Row).Text = "" Or Sh.Cells(1, Target.Column) = "" Or (Target.Row > nRecordCount + 1) Then
Target.Value = oldValue
oldValue = Application.ActiveCell.Value
MsgBox "You can only edit items inside the table"
Exit Sub
End If

' Is this change is in a primary key column - if so, we can't edit it
If (IsInPrimaryKey(Sh.Cells(1, Target.Column).Text)) Then
Target.Value = oldValue
oldValue = Application.ActiveCell.Value
MsgBox "This column is a part of the primary key, so it cannot be changed"
Exit Sub
End If

' Is this change on a Original Forecast Column - if so, we can't edit it
If Then
Target.Value = oldValue
oldValue = Application.ActiveCell.Value
MsgBox "Only The New Forecast can be Changed"
Exit Sub
End If

I need the last If statement to check the row and see if column K has the value "FCST". If it does, the rest of the if needs to execute to reset the cell value.

What do I need here? Something with Target.Row?

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

It looks as if you might be looking for a:

If sh.Cells(Target.Row,"K").Value="FCST" then

Also, I am slightly intrigued by the following line:

If Target.Row < 2 Or Sh.Cells(1, Target.Row).Text = "" Or Sh.Cells(1,  Target.Column) = "" Or (Target.Row > nRecordCount + 1) Then

Sh.Cells(1, Target.Row) is looking in Row=1 and Column=Target.Row. I wpuld have thought that the Target.Row would have been used as a Row index and not as a column index.

Also, not many people use .Text. .Value is the default. .Text tells you what you can actually see on the screen. So if that column is hidden it becomes blank and if the column is made too narrow the value of .Text will change.

 

[jetjaguar15]

Hi and welcome to the MrExcel Message Board.

It looks as if you might be looking for a:

If sh.Cells(Target.Row,"K").Value="FCST" then

Thank you!

However, I am confused. Reading the above statement, I am unclear how Target.Row, "K" is telling it to check column K on the row for the specified value.

 

[RickXL]

Hi,

As you did not post all your code it is a bit of an educated guess.
But such things have worked before

Usually, when you see the Target object used it is in an event handler of some sort. For instance, in a Worksheet Change event:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

When the Worksheet Change event is invoked it passes the value of the range that changed into the event handler. The range is called Target.

So, assuming that the target range is only one cell (and that seems likely because of the code you did publish here) then Target.Row will be the row number of the data that changed.

If that is not the case then I shall be needing some more information.