PDA

View Full Version : 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
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/showthread.php?70211-strAddress-ActiveCell-Address-Run-time-error-438-when-switching-sheets&p=416681&viewfull=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
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
@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 (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
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
Aussiebear
09-07-2022, 03:37 AM
Have you declared "y" as anything?
MSXL
09-07-2022, 03:58 AM
I don't know, it was someone else's proposed code change earlier on in the thread. Based on your question, it's doubtful. I don't know what the answer is as to specifically what it should be declared as.

Top of sheet code


Option Explicit

Public arr As Variant
Public strAddress As String

Original code


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

Suggested, amended to match original? Doesn't work.


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