• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Mouse Settings

Bostbrother

New Member
I have a rather large list of items I want to timestamp. I have a column with all cells values at =now().


Is there a way I can "program" my mouse key to copy the cell and paste as a value with one click instead of having to copy then select "paste special values" then CHOSE "select special values" and then CLICK "OK"? Thanks!
 
Not sure if you can assign this to a mouse key, but "Paste Values" is one of several macros I have shortcuts for built into my personal workbook (Google "Personal workbook", or check XL help file for details on how to set that up.) Within the VB of that workbook, I have my macros setup like below. Here's my top 3 used:


Sub Auto_Open()

Application.OnKey "^+v", "CopyPasteValues" ' Ctrl+Shift+v

Application.OnKey "^+f", "FilterToggle" ' Ctrl+Shift+f

Application.OnKey "^%u", "GetUniqueList" 'Ctrl+Alt+u


End Sub


Sub Auto_Close()


Application.OnKey "^+v"

Application.OnKey "^+f"

Application.OnKey "^%u"

End Sub


Sub CopyPasteValues()

'Ctrl+Shift+P

On Error Resume Next

If TypeName(Selection) = "Range" Then

Selection.PasteSpecial xlPasteValues

End If


End Sub


Sub FilterToggle()

'Ctrl+Shift+F


' add data filter arrows

' if error occurs, just skip

On Error Resume Next

Selection.AutoFilter

End Sub


Sub GetUniqueList()

'Ctrl+Alt+u


Dim rCell As Range

Dim colUnique As Collection

Dim sh As Worksheet

Dim i As Long


'only work on ranges

If TypeName(Selection) = "Range" Then


'create a new collection

Set colUnique = New Collection


'loop through all selected cells

'and add to collection

For Each rCell In Selection.Cells

On Error Resume Next

'if value exists, it won't be added

colUnique.Add rCell.Value, CStr(rCell.Value)

On Error GoTo 0

Next rCell


'make a new sheet to put the unique list

Set sh = ActiveWorkbook.Worksheets.Add


'Write the unique list to the new sheet

For i = 1 To colUnique.Count

sh.Range("A1").Offset(i, 0).Value = colUnique(i)

Next i


'sort with no headers

sh.Range(sh.Range("A2"), sh.Range("A2").End(xlDown)) _

.Sort sh.Range("A2"), xlAscending, , , , , , xlNo


End If


End Sub


Names are somewhat intuitive, but the first macro is to paste special, values; the second is to toggle Autofilter on/off, and the 3rd is to generate a list of unique values. Feel free to delete/add as you desire.


Alternatively, instead of using a formula and then time stamping, you could just hit Ctrl+Shift+;

to insert the time.
 
Back
Top