VBA code problem

[Roger]

Help needed please. I'm trying to use this code as the basis of my double-access price-logging sheet (this code is here:

http://www.gruss-software.co.uk/forum/v ... ing+fields

The problem is, while I can get a small sample of this to work, my main code sheet is getting an error, saying that String, array, etc. cannot be declared as Public in an object module. How do I get round this, please?

Code: Select all

Public sRaceDets As String

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
Dim iCol As Integer
Dim cell As Object

For Each cell In Target
iRow = cell.Row
iCol = cell.Column

' Check for new race being loaded
If iCol = 1 And iRow = 1 Then
If LCase(cell.Value) <> sRaceDets Then
sRaceDets = LCase(cell.Value)

Application.EnableEvents = False


Sheets("Data").Range("T5:x29").Select
Selection.ClearContents
Sheets("Control").Range("A1").Select


Application.EnableEvents = True
End If
End If
Next cell
End Sub


[GaryRussell]

Just change it to Dim instead of public.

Code: Select all

Dim sRaceDets As String

[Roger]

Thanks Gary, I tried that yesterday, but is that variable then preserved between refreshes? Nothing was happening, but I may have something else wrong it seems.

[GaryRussell]

Yes, it is preserved between refreshes. You would use Public in a separate module so it can be accessed between modules. It is not needed or allowed in a worksheet module.

[Roger]

Thanks ever so much Gary. I was trying to run before I could walk! I'm now reading all about debugging in my VBA books, but i'm now sure that my code will eventually do the job. Cheers!

[GaryRussell]

I tested the code and there were other problems. I fixed it to work and it reads as follows.

Code: Select all

Dim sRaceDets As String

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim iRow As Integer
    Dim iCol As Integer
    Dim cell As Object
    For Each cell In Target
        iRow = cell.Row
        iCol = cell.Column
        ' Check for new race being loaded
        If iCol = 1 And iRow = 1 Then
            If LCase(cell.Value) <> sRaceDets Then
                sRaceDets = LCase(cell.Value)
                Sheets("Data").Range("T5:x29").Select
                Selection.ClearContents
                Sheets("Data").Range("T5").Select
                Sheets("Control").Activate
                Sheets("Control").Range("A1").Select
            End If
        End If
    Next cell
    Application.EnableEvents = True
End Sub

[Roger]

Right, see what you've done, thanks. I think my code does the right things in the right order, but I'll try your new version before I get back to mine; if I can get that working it might show me what's wrong. Much obliged, Gary.

[GaryRussell]

The following code is more efficient if you are only checking if the race has changed as it does not involve examining more than 1 cell.

Code: Select all

Dim sRaceDets As String

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    ' Check for new race being loaded
    If LCase(Cells(1, 1).Value) <> sRaceDets Then
        sRaceDets = LCase(Cells(1, 1).Value)
        Sheets("Data").Range("T5:x29").Select
        Selection.ClearContents
        Sheets("Data").Range("T5").Select
        Sheets("Control").Activate
        Sheets("Control").Range("A1").Select
    End If
    Application.EnableEvents = True
End Sub

[Roger]

In my case, I could have a target based on A1 or on A51, and sRaceDets is a string array, so i do need the more complicated version. But in the For Each Cell loop, I come out with an Exit Sub when I find the race details (and I've done everything else), or the code looks as if it goes through all the target cells anyway.

[Roger]

Doh! I found my problem. It was the Exit Sub that was the problem, I wasn't turning on Application.EventEnable again before the exit...