strAddress = .ActiveCell.Address Run-time error 438 when switching sheets

**MSXL** · 09-06-2022, 12:17 AM

Run-time error '438': Object doesn't support this property or method

        strAddress = .ActiveCell.Address

ThisWorkbook

Option Explicit


#If VBA7 Then
    Private Declare PtrSafe Function GetClipboardSequenceNumber Lib "User32" () As Long
#Else
    Private Declare Function GetClipboardSequenceNumber Lib "User32" () As Long
#End If


Private Const TARGET_SHEET As String = "Clipboard"
Private Const TARGET_RANGE As String = "C7,C8,C9,C10,C11,C13,E7,E10,E13,G13,I13,E16,G16,I16,C16,C19,E19,C22" 'Set tab order
Private Const TARGET_INFO_RANGE As String = "C4"


Private Sub Workbook_Open()
    Call ClipOff
    'arnelgp
    'intialize the array
    Sheets("Clipboard").Range("A1").Select
    Clipboard.strAddress = "$C$7"
    Sheets("Clipboard").Select
    Range(Clipboard.strAddress).Select
    
    With Sheet10
        .Unprotect
        .Shapes("Status").TextFrame.Characters.Text = "Input Mode"
        .Protect
    End With
    
    Application.EnableEvents = True
    Application.OnKey "{TAB}", "Clipboard.ProcessTab"
    Application.OnKey "+{TAB}", "Clipboard.ProcessBkTab"
    'Cells included in array
    Clipboard.arr = Array("$C$7", "$C$8", "$C$9", "$C$10", "$C$11", "$C$13", "$E$7", "$E$10", "$E$13", "$G$13", "$I$13", "$E$16", "$G$16", "$I$16", "$C$16", "$C$19", "$E$19", "$C$22")
    
'    Set oCbarEvents = Application.CommandBars
Application.EnableEvents = False
    Dim Sh As Worksheet
    For Each Sh In Worksheets
        If Sh.Name = "Property Numbering" Then
            Sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            Sh.Range("C14,C8").ClearContents
            Sh.Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
            Sh.Range("C14,C8").Value = "'Choose"
        ElseIf Sh.Name = "VO Areas" Then
            Sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            Sh.Range("C4").ClearContents
            Sh.Range("C4").Value = "'Choose"
        Else
            Sh.Protect UserInterFaceOnly:=True
        End If
    Next
Application.EnableEvents = True
End Sub

Clipboard

Option Explicit
Public IsClipRunning As Boolean


Private Sub Worksheet_Activate()
    With Worksheets("Clipboard")
         .Range("C7").Select
        strAddress = .ActiveCell.Address
    End With
    With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = True
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim thisAddress As String
    thisAddress = Split(Target.Address, ":")(0)
    Clipboard.strAddress = Target.Address
    If IsClipRunning Then
        If Len(Trim$(Sheet10.Range(thisAddress).Value & "")) Then
            putToClipboard Sheet10.Range(thisAddress).Value
        End If
    End If
End Sub


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    MsgBox ("To paste data, press ""Ctrl"" & ""V""."), _
    vbInformation, "Automatic Clipboard"
End Sub


Sub PasteasValue()
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Clipboard Module

Option Explicit


Public arr As Variant
Public strAddress As String


Public Sub ProcessTab()
Dim i As Integer
If Len(strAddress) <> 0 Then
    For i = 0 To UBound(arr)
        If arr(i) = Split(strAddress, ":")(0) Then
            If i = UBound(arr) Then
                i = 0
            Else
                i = i + 1
            End If
            Exit For
        End If
    Next
    ActiveSheet.Range(arr(i)).Select
Else
    strAddress = arr(0)
End If
End Sub




Public Sub ProcessBkTab()
Dim i As Integer
If Len(strAddress) <> 0 Then
    For i = 0 To UBound(arr)
        If arr(i) = Split(strAddress, ":")(0) Then
            If i = 0 Then
                i = UBound(arr)
            Else
                i = i - 1
            End If
            Exit For
        End If
    Next
    ActiveSheet.Range(arr(i)).Select
Else
    strAddress = arr(0)
End If
End Sub


Public Function putToClipboard(ByVal theValue As Variant)
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText theValue & ""
        .PutInClipboard
    End With
    Sheet10.Range("$C$4") = theValue
End Function


Public Sub ClipOn()
Dim thisAddress As String
thisAddress = Split(strAddress, ":")(0)
With Sheet10
    .IsClipRunning = True
    ' unprotect and change the color of the "play" button to red (or you may use any color)
    .Unprotect
    .Shapes("Status").TextFrame.Characters.Text = "Copy Mode"
    .Shapes("Status").TextFrame.Characters.Font.Color = vbWhite
    .Shapes("Status").Fill.ForeColor.RGB = vbRed
    .Shapes("Button 33").TextFrame.Characters.Font.Color = vbRed
    .Shapes("Button 34").TextFrame.Characters.Font.Color = vbBlack
    Sheet10.Range("C7,C8,C9,C10,C11,C13,C16,C19,C22,E7:I7,E10:I10,E13,I13,E16,E19:I19,G13,G16,I16").Interior.Color = RGB(242, 242, 242)
    Sheet10.Range("C6:C11,C12:C13,C15:C16,C18:C19,E6:I7,E9:I10,E12:E13,E15:E16,G12:G13,G15:G16,I12:I13,I15:I16,E18:I19,C21:I22").Font.Color = RGB(128, 134, 146)
    .Protect
    If Len(Trim$(.Range(thisAddress).Value & "")) Then
        Call putToClipboard(.Range(thisAddress).Value)
    End If
End With
End Sub


Public Sub ClipOff()
With Sheet10
    ' unprotect to re-instate the color of "play" button to black
    .Unprotect
    .Shapes("Status").TextFrame.Characters.Text = "Input Mode"
    .Shapes("Status").TextFrame.Characters.Font.Color = vbBlack
    .Shapes("Status").Fill.ForeColor.RGB = RGB(146, 208, 80)
    .Shapes("Button 33").TextFrame.Characters.Font.Color = vbBlack
    .Shapes("Button 34").TextFrame.Characters.Font.Color = RGB(146, 208, 80)
    Sheet10.Range("C7:C11,C13,C16,C19,C22:I22,E7:I7,E10:I10,E13,G13,I13,E16,G16,I16,E19:I19").Interior.Color = RGB(146, 208, 80)
    Sheet10.Range("C6:C11,C12:C13,C15:C16,C18:C19,E6:I7,E9:I10,E12:E13,E15:E16,G12:G13,G15:G16,I12:I13,I15:I16,E18:I19,C21:I22").Font.Color = vbBlack
    .IsClipRunning = False
    .Protect
End With
End Sub


Public Sub ClipClear()
Dim Answer As Integer
Answer = MsgBox("Are you sure you wish to clear the data?", vbQuestion + vbYesNo + vbDefaultButton2, "Automatic Clipboard")
If Answer = vbYes Then
    Call ClipOff
'    MsgBox "Cleared"
    Sheet10.Range("C7:C11,C13,C16,C19,C22:I22,E7:I7,E10:I10,E13,I13,E16,G16,I16,E19:I19").ClearContents
    Sheet10.Range("C7:C11,C13,C16,C19,C22,E7:I7,E10:I10,E13,I13,E16,E19:I19,G13,G16,I16").Interior.Color = RGB(146, 208, 80)
    Sheet10.Range("C6:C11,C12:C13,C15:C16,C18:C19,E6:I7,E9:I10,E12:E13,E15:E16,G12:G13,G15:G16,I12:I13,I15:I16,E18:I19,C21:I22").Font.Color = vbBlack
    Sheet10.Range("A1").Select
    Sheet10.Range("C7").Select
    Sheet10.Range("$C$4") = Null
Else
    'Do nothing
End If
End Sub


Sub Help_Click()
    Dim Help As Integer
    Help = MsgBox("Software relies heavily on the Windows clipboard." & Chr(13) & Chr(13) & _
    "If you need to duplicate information to multiple accounts/properties, use this tool." & Chr(13) & Chr(13) & _
    "Type the information you need to copy, then within ""Clipboard Controls"" click """ & Chr(62) & """ and then any cell you click on will automatically be copied to the clipboard." & Chr(13) & Chr(13) & _
    "To input text, click ""||"" and when finished, click """ & Chr(62) & """ to continue copying.", _
    vbOKOnly + vbInformation, "About Automatic Clipboard")
        If Help = vbOK Then
    End If
End Sub

Even if the other worksheet is blank, switching back to Clipboard throws this error.

**georgiboy** · 09-06-2022, 01:02 AM

I would say that sheets: "Clipboard" is not active when the sub:Worksheet_Activate() is being run.

I am saying that as the use of .Select will not work on a sheet that is not active.

Instead of:

With Worksheets("Clipboard")     
    .Range("C7").Select
    strAddress = .ActiveCell.Address
End With

Try:

With Worksheets("Clipboard")
    strAddress = .Range("C7").Address
End With

**arnelgp** · 09-06-2022, 01:24 AM

why not just use:

strAddress = "$C$7"

instead of invoking the .Range and getting it's address?

**snb** · 09-06-2022, 01:53 AM

why not just use:

Sub ProcessTab()
  If straddress <> "" Then
    y = Application.Match(Split(straddress, ":")(0), arr, 0)
    Range(arr(y)).Select
  End If
End Sub

**MSXL** · 09-06-2022, 02:51 AM

Thanks all, so to clarify:

Clipboard:

Option Explicit

Public IsClipRunning As Boolean

Private Sub Worksheet_Activate()
    With Worksheets("Clipboard")
        strAddress = "$C$7"
    End With
    With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = True
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim thisAddress As String
    thisAddress = Split(Target.Address, ":")(0)
    Clipboard.strAddress = Target.Address
    If IsClipRunning Then
        If Len(Trim$(Sheet10.Range(thisAddress).Value & "")) Then
            putToClipboard Sheet10.Range(thisAddress).Value
        End If
    End If
End Sub

Clipboard Module from:

Option Explicit

Public arr As Variant
Public strAddress As String

Public Sub ProcessTab()
Dim i As Integer
If Len(strAddress) <> 0 Then
    For i = 0 To UBound(arr)
        If arr(i) = Split(strAddress, ":")(0) Then
            If i = UBound(arr) Then
                i = 0
            Else
                i = i + 1
            End If
            Exit For
        End If
    Next
    ActiveSheet.Range(arr(i)).Select
Else
    strAddress = arr(0)
End If
End Sub


Public Sub ProcessBkTab()
Dim i As Integer
If Len(strAddress) <> 0 Then
    For i = 0 To UBound(arr)
        If arr(i) = Split(strAddress, ":")(0) Then
            If i = 0 Then
                i = UBound(arr)
            Else
                i = i - 1
            End If
            Exit For
        End If
    Next
    ActiveSheet.Range(arr(i)).Select
Else
    strAddress = arr(0)
End If
End Sub

Public Function putToClipboard(ByVal theValue As Variant)
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText theValue & ""
        .PutInClipboard
    End With
    Sheet10.Range("$C$4") = theValue
End Function

Public Sub ClipOn()
Dim thisAddress As String
thisAddress = Split(strAddress, ":")(0)
With Sheet10
    .IsClipRunning = True
    ' unprotect and change the color of the "play" button to red (or you may use any color)
    .Unprotect
    .Shapes("Status").TextFrame.Characters.Text = "Copy Mode"
    .Shapes("Status").TextFrame.Characters.Font.Color = RGB(128, 134, 146)
'    .Shapes("Status").Fill.ForeColor.RGB = vbRed
    .Shapes("Status").Fill.ForeColor.RGB = RGB(242, 242, 242)
    .Shapes("Button 33").TextFrame.Characters.Font.Color = RGB(128, 134, 146)
    .Shapes("Button 34").TextFrame.Characters.Font.Color = vbBlack
    Sheet10.Range("C7,C8,C9,C10,C11,C13,C16,C19,C22,E7:I7,E10:I10,E13,I13,E16,E19:I19,G13,G16,I16").Interior.Color = RGB(242, 242, 242)
    Sheet10.Range("C6:C11,C12:C13,C15:C16,C18:C19,E6:I7,E9:I10,E12:E13,E15:E16,G12:G13,G15:G16,I12:I13,I15:I16,E18:I19,C21:I22").Font.Color = RGB(128, 134, 146)
    .Protect
    If Len(Trim$(.Range(thisAddress).Value & "")) Then
        Call putToClipboard(.Range(thisAddress).Value)
    End If
End With
End Sub

Public Sub ClipOff()
With Sheet10
    ' unprotect to re-instate the color of "play" button to black
    .Unprotect
    .Shapes("Status").TextFrame.Characters.Text = "Input Mode"
    .Shapes("Status").TextFrame.Characters.Font.Color = vbBlack
    .Shapes("Status").Fill.ForeColor.RGB = RGB(146, 208, 80)
    .Shapes("Button 33").TextFrame.Characters.Font.Color = vbBlack
    .Shapes("Button 34").TextFrame.Characters.Font.Color = RGB(146, 208, 80)
    Sheet10.Range("C7:C11,C13,C16,C19,C22:I22,E7:I7,E10:I10,E13,G13,I13,E16,G16,I16,E19:I19").Interior.Color = RGB(146, 208, 80)
    Sheet10.Range("C6:C11,C12:C13,C15:C16,C18:C19,E6:I7,E9:I10,E12:E13,E15:E16,G12:G13,G15:G16,I12:I13,I15:I16,E18:I19,C21:I22").Font.Color = vbBlack
    .IsClipRunning = False
    .Protect
End With
End Sub

Public Sub ClipClear()
Dim Answer As Integer
Answer = MsgBox("Are you sure you wish to clear the data?", vbQuestion + vbYesNo + vbDefaultButton2, "Automatic Clipboard")
If Answer = vbYes Then
    Call ClipOff
'    MsgBox "Cleared"
    Sheet10.Range("C7:C11,C13,C16,C19,C22:I22,E7:I7,E10:I10,E13,I13,E16,G16,I16,E19:I19").ClearContents
    Sheet10.Range("C7:C11,C13,C16,C19,C22,E7:I7,E10:I10,E13,I13,E16,E19:I19,G13,G16,I16").Interior.Color = RGB(146, 208, 80)
    Sheet10.Range("C6:C11,C12:C13,C15:C16,C18:C19,E6:I7,E9:I10,E12:E13,E15:E16,G12:G13,G15:G16,I12:I13,I15:I16,E18:I19,C21:I22").Font.Color = vbBlack
    Sheet10.Range("A1").Select
    Sheet10.Range("C7").Select
    Sheet10.Range("$C$4") = Null
Else
    'Do nothing
End If
End Sub

Sub Help_Click()
    Dim Help As Integer
    Help = MsgBox("Software relies heavily on the Windows clipboard." & Chr(13) & Chr(13) & _
    "If you need to duplicate information to multiple accounts/properties, use this tool." & Chr(13) & Chr(13) & _
    "Type the information you need to copy, then within ""Clipboard Controls"" click """ & Chr(62) & """ and then any cell you click on will automatically be copied to the clipboard." & Chr(13) & Chr(13) & _
    "To input text, click ""||"" and when finished, click """ & Chr(62) & """ to continue copying.", _
    vbOKOnly + vbInformation, "About Automatic Clipboard")
        If Help = vbOK Then
    End If
End Sub

Clipboard Module to (removing "Public"?):

Option Explicit

Public arr As Variant
Public strAddress As String

Sub ProcessTab()   If straddress <> "" Then
    y = Application.Match(Split(straddress, ":")(0), arr, 0)
    Range(arr(y)).Select
  End If
End Sub

Public Sub ProcessBkTab()
Dim i As Integer
If Len(strAddress) <> 0 Then
    For i = 0 To UBound(arr)
        If arr(i) = Split(strAddress, ":")(0) Then
            If i = 0 Then
                i = UBound(arr)
            Else
                i = i - 1
            End If
            Exit For
        End If
    Next
    ActiveSheet.Range(arr(i)).Select
Else
    strAddress = arr(0)
End If
End Sub

Public Function putToClipboard(ByVal theValue As Variant)
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText theValue & ""
        .PutInClipboard
    End With
    Sheet10.Range("$C$4") = theValue
End Function

Public Sub ClipOn()
Dim thisAddress As String
thisAddress = Split(strAddress, ":")(0)
With Sheet10
    .IsClipRunning = True
    ' unprotect and change the color of the "play" button to red (or you may use any color)
    .Unprotect
    .Shapes("Status").TextFrame.Characters.Text = "Copy Mode"
    .Shapes("Status").TextFrame.Characters.Font.Color = RGB(128, 134, 146)
'    .Shapes("Status").Fill.ForeColor.RGB = vbRed
    .Shapes("Status").Fill.ForeColor.RGB = RGB(242, 242, 242)
    .Shapes("Button 33").TextFrame.Characters.Font.Color = RGB(128, 134, 146)
    .Shapes("Button 34").TextFrame.Characters.Font.Color = vbBlack
      Sheet10.Range("C7,C8,C9,C10,C11,C13,C16,C19,C22,E7:I7,E10:I10,E13,I13,E16,E19:I19,G13,G16,I16").Interior.Color  = RGB(242, 242, 242)
     Sheet10.Range("C6:C11,C12:C13,C15:C16,C18:C19,E6:I7,E9:I10,E12:E13,E15:E16,G12:G13,G15:G16,I12:I13,I15:I16,E18:I19,C21:I22").Font.Color  = RGB(128, 134, 146)
    .Protect
    If Len(Trim$(.Range(thisAddress).Value & "")) Then
        Call putToClipboard(.Range(thisAddress).Value)
    End If
End With
End Sub

Public Sub ClipOff()
With Sheet10
    ' unprotect to re-instate the color of "play" button to black
    .Unprotect
    .Shapes("Status").TextFrame.Characters.Text = "Input Mode"
    .Shapes("Status").TextFrame.Characters.Font.Color = vbBlack
    .Shapes("Status").Fill.ForeColor.RGB = RGB(146, 208, 80)
    .Shapes("Button 33").TextFrame.Characters.Font.Color = vbBlack
    .Shapes("Button 34").TextFrame.Characters.Font.Color = RGB(146, 208, 80)
    Sheet10.Range("C7:C11,C13,C16,C19,C22:I22,E7:I7,E10:I10,E13,G13,I13,E16,G16,I16,E19:I19").Interior.Color = RGB(146, 208, 80)
      Sheet10.Range("C6:C11,C12:C13,C15:C16,C18:C19,E6:I7,E9:I10,E12:E13,E15:E16,G12:G13,G15:G16,I12:I13,I15:I16,E18:I19,C21:I22").Font.Color  = vbBlack
    .IsClipRunning = False
    .Protect
End With
End Sub

Public Sub ClipClear()
Dim Answer As Integer
Answer = MsgBox("Are you sure you wish to clear the data?", vbQuestion + vbYesNo + vbDefaultButton2, "Automatic Clipboard")
If Answer = vbYes Then
    Call ClipOff
'    MsgBox "Cleared"
    Sheet10.Range("C7:C11,C13,C16,C19,C22:I22,E7:I7,E10:I10,E13,I13,E16,G16,I16,E19:I19").ClearContents
    Sheet10.Range("C7:C11,C13,C16,C19,C22,E7:I7,E10:I10,E13,I13,E16,E19:I19,G13,G16,I16").Interior.Color = RGB(146, 208, 80)
      Sheet10.Range("C6:C11,C12:C13,C15:C16,C18:C19,E6:I7,E9:I10,E12:E13,E15:E16,G12:G13,G15:G16,I12:I13,I15:I16,E18:I19,C21:I22").Font.Color  = vbBlack
    Sheet10.Range("A1").Select
    Sheet10.Range("C7").Select
    Sheet10.Range("$C$4") = Null
Else
    'Do nothing
End If
End Sub

Sub Help_Click()
    Dim Help As Integer
    Help = MsgBox("Software relies heavily on the Windows clipboard." & Chr(13) & Chr(13) & _
    "If you need to duplicate information to multiple accounts/properties, use this tool." & Chr(13) & Chr(13) & _
     "Type the information you need to copy, then within ""Clipboard  Controls"" click """ & Chr(62) & """ and then any cell you click  on will automatically be copied to the clipboard." & Chr(13) &  Chr(13) & _
    "To input text, click ""||"" and when finished, click """ & Chr(62) & """ to continue copying.", _
    vbOKOnly + vbInformation, "About Automatic Clipboard")
        If Help = vbOK Then
    End If
End Sub

**snb** · 09-06-2022, 03:03 AM

It's still nonsense:

    With Worksheets("Clipboard")
        strAddress = "$C$7"
    End With

just

strAddress = "$C$7"

**MSXL** · 09-06-2022, 03:14 AM

Thank you, so the idea is that the code is shortened?

Clipboard:

Option Explicit

Public IsClipRunning As Boolean

Private Sub Worksheet_Activate()
strAddress = "$C$7"
    With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = True
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim thisAddress As String
    thisAddress = Split(Target.Address, ":")(0)
    Clipboard.strAddress = Target.Address
    If IsClipRunning Then
        If Len(Trim$(Sheet10.Range(thisAddress).Value & "")) Then
            putToClipboard Sheet10.Range(thisAddress).Value
        End If
    End If
End Sub

**georgiboy** · 09-06-2022, 03:23 AM

Will need to restructure the below part as well, not sure if it is in your code or it happened when pasting it across:

Sub ProcessTab()   If straddress <> "" Then    
    y = Application.Match(Split(straddress, ":")(0), arr, 0)
    Range(arr(y)).Select
  End If
End Sub

If you are still going to use it that is.

**MSXL** · 09-06-2022, 03:33 AM

Originally Posted by georgiboy

Will need to restructure the below part as well, not sure if it is in your code or it happened when pasting it across:

Sub ProcessTab()   If straddress <> "" Then    
    y = Application.Match(Split(straddress, ":")(0), arr, 0)
    Range(arr(y)).Select
  End If
End Sub

If you are still going to use it that is.

Thanks for your reply. That was code proposed by snb:

http://www.vbaexpress.com/forum/show...l=1#post416681

**georgiboy** · 09-06-2022, 03:40 AM

I think that happens when we paste our code into the forum - it is not intentional.

Should be:

Sub ProcessTab()    
    If straddress <> "" Then
        y = Application.Match(Split(straddress, ":")(0), arr, 0)
        Range(arr(y)).Select
    End If
End Sub

**MSXL** · 09-06-2022, 03:53 AM

Thank you. So it is right to remove "Public" from the sub?

I am thinking if that code has been changed, it would make sense to also change it for

Public Sub ProcessBkTab()

but I'm not sure how that would translate.

Public Sub ProcessBkTab()
Dim i As Integer
If Len(strAddress) <> 0 Then
    For i = 0 To UBound(arr)
        If arr(i) = Split(strAddress, ":")(0) Then
            If i = 0 Then
                i = UBound(arr)
            Else
                i = i - 1
            End If
            Exit For
        End If
    Next
    ActiveSheet.Range(arr(i)).Select
Else
    strAddress = arr(0)
End If
End Sub

**snb** · 09-06-2022, 05:53 AM

Originally Posted by georgiboy

Will need to restructure the below part as well, not sure if it is in your code or it happened when pasting it across:

I fear it has to do with your browser, here (firefox) the code is completely normal.

**georgiboy** · 09-06-2022, 06:17 AM

@snb

Nothing to do with me or my end, look at post 5 - you can see how it is there.

Sometimes after pasting code it happens when I click 'Post Quick Reply' then if you edit the post within a set time you don't get the message on the post stating that it has been edited. The OP could have copied it before it was edited?

**snb** · 09-06-2022, 07:21 AM

@georgi

It wasn't edited as you can see.

**georgiboy** · 09-06-2022, 07:34 AM

@snb I think you have missed one of the points I made in post 13 (if you edit the post within a set time you don't get the message on the post stating that it has been edited)

I am not really bothered how it got there to be honest - the OP has it in his post (I imagine it was copied that way). I was just pointing out that the code will not work as is.

It is a common thing on here when using code tags that when the code is posted it sometimes joins the first line of code to the sub line. Not sure why it happens but it does look as if that is what has happened at some point.

**MSXL** · 09-06-2022, 08:07 AM

Originally Posted by georgiboy

@snb I think you have missed one of the points I made in post 13 (if you edit the post within a set time you don't get the message on the post stating that it has been edited)

I am not really bothered how it got there to be honest - the OP has it in his post (I imagine it was copied that way). I was just pointing out that the code will not work as is.

It is a common thing on here when using code tags that when the code is posted it sometimes joins the first line of code to the sub line. Not sure why it happens but it does look as if that is what has happened at some point.

I appreciate you pointing it out because I didn't know about this issue.

Could you please advise on http://www.vbaexpress.com/forum/showthread.php?70211-strAddress-ActiveCell-Address-Run-time-error-438-when-switching-sheets&p=416689&viewfull=1#post416689 ?

**MSXL** · 09-06-2022, 05:20 PM

Does is surprise you that I am getting errors with this?

Sub ProcessTab()        
If straddress <> "" Then
        y = Application.Match(Split(straddress, ":")(0), arr, 0)
        Range(arr(y)).Select
    End If
End Sub

In particular, it's highlghting "y =" when I press tab in any other worksheet with "Compile Error: Variable Not Defined".

**Aussiebear** · 09-06-2022, 07:04 PM

Could well be because early on you had declared straddress as string within a "Public sub", and then you were on about taking "Public" out. Simply dim it as string within this module.

**MSXL** · 09-06-2022, 11:50 PM

Originally Posted by Aussiebear

Could well be because early on you had declared straddles as string within a "Public sub", and then you were on about taking "Public" out. Simply dim it as string within this module.

Thanks for your reply. In the example code provided, Public was taken out and I had asked if that was intentional but didn't get an answer. Can you please show me what it would look like as a dim string?

Doesn't work, stops tab from functioning at all.

Public Sub ProcessTab()
Dim y As String
    If strAddress <> "" Then
        y = Application.Match(Split(strAddress, ":")(0), arr, 0)
        Range(arr(y)).Select
    End If
End Sub

**MSXL** · 09-07-2022, 12:31 AM

Looking at the Clipboard module, it looks very similar to what you say?

Option Explicit

Public arr As Variant
Public strAddress As String

Public Sub ProcessTab()

    If strAddress <> "" Then
        y = Application.Match(Split(strAddress, ":")(0), arr, 0)
        Range(arr(y)).Select
    End If
End Sub

Thread: strAddress = .ActiveCell.Address Run-time error 438 when switching sheets

Thread Tools

Display

strAddress = .ActiveCell.Address Run-time error 438 when switching sheets

Posting Permissions