Setting tab order, adding ability to stop/start code and refine functionality

[MSXL]

I'm working on a concept to create an automatic clipboard, working from one sheet only.

First feature needed is to be able to set the tab order C7,C8,C9,C10,C11,C13,C16,C19,E7,E10,E13,G13,I13,E16,G16,I16,E19,C22 permanently regardless of whether any data is input into the existing cell. It should be possible to also click on a cell and when tab is pressed, it goes on to the next cell in this list following the selected cell. So for example, the user selects C13 and presses tab. It should then move on to C16.

Once the user is ready, code should start enabling a user to select any cell and automatically copy the contents to the clipboard. The user should be able to pause the code at any point to be able to paste data from elsewhere into the worksheet or type new data. I've added control buttons with the intention of those controlling start/stop.

At the moment, I have it working in part and have attached a copy so you can see what I am working with.

You will see that currently using tab messes up what is copied to the clipboard, clicking on a cell doesn't work as expected and for reasons I cannot fathom, it does not ever tab through to C22.

https://drive.google.com/file/d/1Pob...ew?usp=sharing

Thank you for your help.

[arnelgp]

what i worked on was the Tab/ Shift-Tab.
you need to add code for the Clipboard thing.

[MSXL]

Thank you for your reply. I tried testing the attached and when I pressed tab, it immediately came up with an error:

Tun-time error '13':
Type mismatch

For i = 0 To UBound(arr)

[arnelgp]

obviously the code in Thisworkbook Open did not fire-up.
close the workbook first.
upon opening, if you see any Macro Warning, (yellow band) just enable it.

[arnelgp]

here i select $C$7 first on Clipboard sheet.
then, you can Tab/Shift tab.

[MSXL]

Thank you, that's awesome. You even considered back tabbing, which I hadn't.

I'm not sure how to re-introduce the automatic copy functionality, as trying to reinsert the code where it was or even reducing it to "Target.Copy" after module1 brings up an error within module1

Clipboard code:

If Not Intersect(Target, Range("C7,C8,C9,C10,C11,C13,C16,C19,E7,E10,E13,G13,I13,E16,G16,I16,E19,C22")) Is Nothing Then        Target.Copy
    End If

Error line with mismatch:

    strAddress = arr(0)

[MSXL]

Rethinking it, this is what I have come up with:

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
    ActiveSheet.Range.Copy
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
    ActiveSheet.Range.Copy
Else
    strAddress = arr(0)
End If
End Sub

I think it might be best to change ActiveSheet to the worksheet name so it doesn't throw an error if the user switches between workbooks.

It's coming back up with the same mismatch error when trying to select different cells or tabbing now.

https://drive.google.com/file/d/1BuT...ew?usp=sharing

[MSXL]

Changing to the worksheet name doesn't work:

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
    Worksheets("Clipboard").Range(arr(i)).Select
    Worksheets("Clipboard").Range(arr(i)).Copy
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
    Worksheets("Clipboard").Range(arr(i)).Select
    Worksheets("Clipboard").Range(arr(i)).Copy
Else
    strAddress = arr(0)
End If
End Sub

[MSXL]

Seems to work until try clicking a cell, the focus doesn't change to that cell and the clipboard remains on the last selection made by tabbing.

[MSXL]

Modifying the VBA on the worksheet Clipboard seems to fix this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    Module1.strAddress = Target.Address
    Target.Copy
End Sub

Now need to figure out how to disable automatic copying using the buttons to allow user to paste data from an external source or type it in and tab without copying.

Also, trying to avoid empty cells from being copied when automatic copying is enabled.

[MSXL]

Thought this might be a partial solution but it didn't make any difference:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    Module1.strAddress = Target.Address
    If Not IsEmpty(Target.Address) Then
    Target.Copy
    End If
End Sub

[arnelgp]

why do you need to have the Clipboard involve, when you can Reference any cell and check if it has value.
then you assign the value to an array/variable.

when it is time to "paste" just read out from the variables.

EDIT:

what does the "play", "pause", "refresh" buttons need to do?
maybe i can help you out, without the Clipboard thing.

[MSXL]

Thought it might be something to do with needing to introduce similar in the module but unsure how to phrase what should be inserted in brackets. I tried a few things and none of them worked including "Range(arr(i))"

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
    Worksheets("Clipboard").Range(arr(i)).Select
    If Not IsEmpty() = True Then
    Worksheets("Clipboard").Range(arr(i)).Copy
    End If
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
    Worksheets("Clipboard").Range(arr(i)).Select
    If Not IsEmpty() = True Then
    Worksheets("Clipboard").Range(arr(i)).Copy
    End If
Else
    strAddress = arr(0)
End If
End Sub

[MSXL]

Thanks for your reply. The play button is for VBA to loop continuously providing the clipboard automatically copying functionality, the pause button is to allow users to type or "paste special-value" text into the cells without the VBA loop interrupting. The refresh button is to clear all the cells of values.

[MSXL]

It might be worth me explaining that the main and original idea was to be able to click any cell that contains data and have it automatically copy to the clipboard, so that it can be pasted quickly to another application.

[arnelgp]

here check and test again those 3 buttons.

[MSXL]

w00t! I've played with it a little bit and it seems to work exactly as I had imagined. I have no idea how you did that! Thank you. I will let you know if I come across any issues.

[MSXL]

Is there a way to insert the words "Input Mode" in the space between the pause and undo buttons when the worksheet first opens or pause is pressed and "Copy Mode" when the play button is pressed (centred in I3).

[arnelgp]

here i added "Status" rectangle shape.

[MSXL]

In the mean time, I have added the following to Clipboard VBA

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

Within Module1, I'd like to add a prompt "Are you sure you want to clear all the data? Yes/No.

If yes, clear. If no, cancel.

I figure that would go in here.

Public Sub ClipClear()
Call ClipOff
Sheet10.Range("$C$4") = Null
End Sub

vbYesNo with default button No vbDefaultButton2.