I'm wanting to move to a specific worksheet using the worksheet name copied from the Clipboard to a variable. I can get the text string from the clipboard and into a variable. I can MsgBox it so it looks right. But if I try to move to that worksheet using the variable that is set from the clipboard I get the Run-time error '9' Subscript out of range. If I set the variable with VBA, then it moves to the correct worksheet. But it won't work if I set the variable from the clipboard--even though I can Msgbox it and the value is correct. Here is my code.
Option Explicit Public xText As String Public Sub MoveToSheet()
Dim CObj As MSForms.DataObject Set CObj = New MSForms.DataObject CObj.GetFromClipboard xText = CObj.GetText(1) MsgBox xText '(This displays the correct value) 'xText = "q2" '(If I set the variable here then the next line works. Worksheets(xText).Select
End Sub
Kevin Robertson
@Reply 8 months ago
I just tested your code and it worked fine for me.
- I inserted a module and pasted in your code.
- I added a reference to : Microsoft Forms 2.0 Object Library.
- I added a shape to one of my sheets.
- I assigned the Macro to the shape.
- I copied the Sheet name to my clipboard.
- I clicked on the shape.
- It displayed the Message Box and went to the correct sheet.
Donald Blackwell
@Reply 8 months ago
Even though your msgbox shows what you're expecting, maybe it has some extra space characters...
Possibly try setting:
xText = trim(CObj.GetText(1))
and see if that helps
Donald Blackwell
@Reply 8 months ago
Ahh, Kevin beat me, lol. And he's probably got the better answer since I don't work in Excel that much.
Kevin Robertson
@Reply 8 months ago
Here's some other code you could try that doesn't involve making a reference to a Library.
DetailsOption Explicit
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As LongPtr
Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As Long
Private Declare PtrSafe Function lstrlenW Lib "kernel32" (ByVal lpString As LongPtr) As Long
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
ByVal Destination As LongPtr, ByVal Source As LongPtr, ByVal Length As LongPtr)
Private Const CF_UNICODETEXT As Long = 13&
Public xText As String
Private Function GetClipboardText() As String
Dim s As String
Dim hClip As LongPtr, pText As LongPtr
Dim cch As Long
If IsClipboardFormatAvailable(CF_UNICODETEXT) = 0 Then Exit Function
If OpenClipboard(0) = 0 Then Exit Function
On Error GoTo CleanUp
hClip = GetClipboardData(CF_UNICODETEXT)
If hClip <> 0 Then
pText = GlobalLock(hClip)
If pText <> 0 Then
cch = lstrlenW(pText) ' length in wide chars (no null)
If cch > 0 Then
s = String$(cch, vbNullChar)
CopyMemory StrPtr(s), pText, cch * 2&
GetClipboardText = s
End If
GlobalUnlock hClip
End If
End If
CleanUp:
CloseClipboard
End Function
Public Sub MoveToSheet()
On Error GoTo ErrHandler
xText = Trim$(GetClipboardText())
If Len(xText) = 0 Then
MsgBox "Clipboard is empty or not text.", vbExclamation
Exit Sub
End If
' Try to activate a matching sheet (case-insensitive, trims stray CR/LF)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If StrComp(ws.Name, xText, vbTextCompare) = 0 Then
ws.Activate
Exit Sub
End If
Next
MsgBox "Worksheet '" & xText & "' not found in this workbook.", vbExclamation
Exit Sub
ErrHandler:
MsgBox "Error: " & Err.Description, vbExclamation
End Sub
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Excel Forum.
