ukphoenix
05-04-2008, 02:54 AM
Im in need of a macro god to help solve my problem. I have a current macro attached to a workbook that I need to add too. The issue is I dont know what to add or where to add it.
Currently the macro disables the cut, copy, paste features and the protection is on which I want left.
I now need either the ability to sort data within a particular column in ascending order.
ie column A(letters) & column B(numbers) read as so A,1 B,3 C,4 D,2 I need to be able to sort them A,1 D,2 B,3 C,4
Or if the sort cannot be used the macro to sort them for me on entering
ie using the same form as above, as I enter D,2 it auto shuffles to read in the right place as above
Here's the current macro
Option Explicit
Public CloseDownTime As Variant
Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial
Application.CommandBars("Worksheet ").Controls("Tools").Controls("Macro").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("Edit").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("format").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("data").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("Tools").Enabled = True
'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow
'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub
Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub
Public Sub ResetTimer()
On Error Resume Next
If Not IsEmpty(CloseDownTime) Then Application.OnTime EarliestTime:=CloseDownTime, _
Procedure:="CloseDownFile", Schedule:=False
CloseDownTime = Now + TimeValue("00:04:59") ' hh:mm:ss
Application.OnTime CloseDownTime, "CloseDownFile"
End Sub
Public Sub CloseDownFile()
On Error Resume Next
Application.StatusBar = "Inactive File Closed: " & ThisWorkbook.Name
ThisWorkbook.Close SaveChanges:=True
End Sub
Edit: Use the VBA button instead of quote to format your code correctly
Currently the macro disables the cut, copy, paste features and the protection is on which I want left.
I now need either the ability to sort data within a particular column in ascending order.
ie column A(letters) & column B(numbers) read as so A,1 B,3 C,4 D,2 I need to be able to sort them A,1 D,2 B,3 C,4
Or if the sort cannot be used the macro to sort them for me on entering
ie using the same form as above, as I enter D,2 it auto shuffles to read in the right place as above
Here's the current macro
Option Explicit
Public CloseDownTime As Variant
Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial
Application.CommandBars("Worksheet ").Controls("Tools").Controls("Macro").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("Edit").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("format").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("data").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("Tools").Enabled = True
'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow
'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub
Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub
Public Sub ResetTimer()
On Error Resume Next
If Not IsEmpty(CloseDownTime) Then Application.OnTime EarliestTime:=CloseDownTime, _
Procedure:="CloseDownFile", Schedule:=False
CloseDownTime = Now + TimeValue("00:04:59") ' hh:mm:ss
Application.OnTime CloseDownTime, "CloseDownFile"
End Sub
Public Sub CloseDownFile()
On Error Resume Next
Application.StatusBar = "Inactive File Closed: " & ThisWorkbook.Name
ThisWorkbook.Close SaveChanges:=True
End Sub
Edit: Use the VBA button instead of quote to format your code correctly