Right so in a nutshell I've got a load of active X command buttons which are used to:
1. Copy a VBA Generated SQL from a Cell (each with a named range with a common prefix followed by a number)
2. Activate the sheet which the user should paste the results of the Copied SQL to
The process they all go through is fairly common so the only thing I have each of the actual button controls doing is setting a suffix number from 1 to 10 which then passes this to a common bit of code:
Option Explicit
Private Sub cmdSQL_1_Click()
Dim sSQLNumber As String
sSQLNumber = 1
Call SQL_To_Clipboard(sSQLNumber)
End Sub
Sub SQL_To_Clipboard(ByRef sSQLNumber)
Dim sSQLName As String
Dim sSheetCodeName As String
sSQLName = "DynamSQL_" & sSQLNumber
sSheetCodeName = "SQL" & sSQLNumber
'Copies the contents of the named range onto the clipboard
With New DataObject
.SetText DynamSQLOut.Range(sSQLName).Text
.PutInClipboard
End With
'Activate the relevant worksheet for SQL Output
'THIS IS THE BIT THAT DOESN'T WORK
sSheetCodeName.Activate
sSheetCodeName.Cells(2, 1).Select
End Sub
Display More
So the problem is at the end, part where I'm trying to activate the relevant SQL Output Sheet. The codenames of these sheets are all SQL1-10.
I appreciate the above doesn't work because the codename isn't a string so I can't use this to reference the sheet in the same way you could use a string to reference a named range ( i.e. .range(StringVariable) can be used to replace .range("Value of StringVariable") ).
But is there anyway I can get it to recognise a variable as a worksheet codename.
Obviously one solution could be to use the worksheet name rather than the codename and then the above, in theory (if sSheetCodeName was replaced with Sheets(sSheetCodeName) ), would work fine if sSheetCodeName had the same value as the worksheet name. But I can't really do it that way as the worksheets are not generically named for the purposes of the user.
Any ideas folks?