Click and drag VBA?

Hello!

We have a project that requires inputting names into a schedule. Right now, the users are typing the names in. I have pre-loaded the names into hidden rows above the columns so that the user only has to input enough unique letters to identify the name. However, it is still time-consuming.

Control-dragging the upper corner of each cell works from the names column works, but it is difficult to always grab the upper corner, and it copies the conditional formatting, too. It also requires another mouse click to select the starting cell.

We would like a simpler way to copy values from one cell to another. One idea would be to launch a macro, click on a cell, and clicking on a second cell automatically copies values to the second cell. This could continue until the macro is stopped. And the technique must NOT copy formatting to the destination cell.

Any ideas appreciated!

If you want to have a vba solution you can do something like this
Map Switchmode to a button for activating and deactivating the copy mode (you could also use a cell reference instead a Public variable)
Then declare the range where your namelist is located in my case in column A

When someone doubleclicks on anything in column A whole copymode is activated the text in that column will be placed in any cell that is selected outside of coulmn A

Public Copyname As String
Public copymode As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If copymode = True And Target.Column = 1 Then
    Copyname = Target.Value
    Cancel = True
End If
End Sub

Sub switchmode()
copymode = Not copymode
Debug.Print copymode
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If copymode = True And Target.Column <> 1 Then
    Target.Value = Copyname
    Cancel = True
End If
End Sub

Sure you can change the range to cover every space on the sheet. You could also change it so that it only pastes the names inside a certain area.
I could change the button text too I will add an update tomorrow cause at the moment I am in the train.

Greets
Loki

Originally Posted by Hambone70

LordLoki - that's awesome! That's exactly the kind of solution we're looking for.

Can I further refine the namelist range as rectangle of cells rather than a column?

And is there a way to have the text on the button toggle between "Start Copying" and "Stop Copying"? Otherwise, I can have the macro change the text in an adjacent cell, or a color.

Originally Posted by LordLoki

cause at the moment I am in the train.

Sounds painful

Good Morning,

@inq no clue what you are talking about :D

@Hambone i made a few Changes to the code all you need to do is put the code on the worksheet where your shedule is then add an ActiveX Button to the sheet
After that go to the code and put the name of that button in the 3 "buttonname" variables and specify the ranges correctly
Then it should work as you wanted

The text Of the Button and the Color will Change
You can define any Range on the Worksheet as Name Range
You can Define Any Range on the Sheet as Shedule Range
And you need to Define one Cell as Reference to the Active Name

I removed the Public Variables so the Current name is refered by a Cell and the Current Mode (Copy or Not) is Determined by the Color of the Button.

Have Fun with it

Private Sub CommandButton21_Click()
Dim Buttonname As String

'Just insert your Buttonname here and make sure its an ActiveX Button not a Form Button
Buttonname = "CommandButton21"


If ActiveSheet.OLEObjects(Buttonname).Object.BackColor <> vbRed Then
    Me.OLEObjects(Buttonname).Object.Caption = "Stop Copymode" 'change this text if needed
    Me.OLEObjects(Buttonname).Object.BackColor = vbRed
Else
    Me.OLEObjects(Buttonname).Object.Caption = "Start Copymode" 'change this text if needed
    Me.OLEObjects(Buttonname).Object.BackColor = vbGreen
End If

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Namelist As Range
Dim activename As Range
Dim Buttonname As String

'Insert The name of the Button again and define the Range where your List of names is located
'Also Specify a Cell that holds the current active name
Buttonname = "CommandButton21"
Set Namelist = Me.Range("A5:B30")
Set activename = Me.Range("C2")

If Me.OLEObjects(Buttonname).Object.BackColor = vbRed And Not Intersect(Target, Namelist) Is Nothing Then
    activename.Value = Target.Value
    Cancel = True
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Shedulerange As Range
Dim Buttonname As String

'And once again the name of your Button and activename
'and the range where it should paste names when selected
Buttonname = "CommandButton21"
Set Shedulerange = Me.Range("D5:I20")
Set activename = Me.Range("C2")

If Me.OLEObjects(Buttonname).Object.BackColor = vbRed And Not Intersect(Target, Shedulerange) Is Nothing Then
    Target.Value = activename.Value
    Cancel = True
End If

End Sub

The File i attached should work out of the box.
If you put the code in your workbook you would need to change the variables for ranges etc.
I did not know you copy the sheet for that i would change some references.

Will check that later and upload a new code.
Do you get an error using the last uploaded sheet?
What exactly happens?