Solved: Need to add a sort command to macro

[ukphoenix]

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

[vba]

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

[/vba]
Edit: Use the VBA button instead of quote to format your code correctly

[Simon Lloyd]

You say Prtection is on but there is no protect routine in your code! when you say sort, do you want to sort on entry, on open, on action of the above, on close....or what?
If you want to sort on entry then put this in the worksheet module you are working with:
[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub[/VBA]

[ukphoenix]

The worksheet itself is password protected which causes the sort function to be disabled. I require this function enabled so that certain columns can be sorted but the sheet in general remains protected.
The colums in question need to remain together
ie a1 & b1 info stay inline but the data may be sorted accordingly (im crap at explaining check the sample).
the list is as follows
Columns
A1 B1
a - 1
b - 2
d - 4
c - 3
a - 5
but need to be able to sort the data to read
a - 1
b - 2
c - 3
d - 4
a - 5
hope that makes a bit more sense. SO i would need to sort the data in ascending order in column B1. If protection is off you would just highlight the data in columns A1 and B1 then choose sort column B.

[rbrhodes]

Hi ukp,

Something like this may get you started. Post back if you need more...

[VBA]
Option Explicit

Sub AllowSort()

'//Change sheet name and password here
Sheets("Sheet1").Unprotect "bob"

'//Sort Col B
Columns("B:B").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'//Change sheet name and password here
Sheets("Sheet1").Protect password:="bob"

End Sub

[/VBA]

[ukphoenix]

Sorry rbr cant get this to work i can send u the original workbook if you want to try and get something working

[mdmackillop]

You can post your workbook using Manage Attachments in the Go Advanced section

[ukphoenix]

Ok heres the workbook if anyone wants to have a play with it and try and solve the issue I have
The password for the sheet is 'bob' and the problem set is on the 'Hourly' sheet

[rangudu_2008]

Add data to the ID column in the attached workbook and click the button... Use the code as in this example workbook i've created in ur file...

[ukphoenix]

Im now gonna plead my stupidity here.... I can see what your macro does but cant see where I place it within my workbook for it to work.
As I said im new to macros and need all the help I can get.

Is there anyone that can get this to work for me. I like the idea of a sort button (would need to be placed at D26 on the hourly sheet). Im the type of person that needs to have it working to understand it or change it.

[rbrhodes]

Hey UK,

No wonder you couldn't get it to work there's a lot of code happening in that WB!

Try this out.

I presumend you wanted to sort Cols B thru N. To do this I had to modify your layout slightly as the 'Sort' function _chokes_ on merged cells (in fact Merged cells are the nemisis of VBA!!!!!). I killed Columns K & L to allow the Sort to function. This is the 'NEW' workbook.

If this is not true and you only want to sort Cols B & C then I can give you back your layout with the cells merged. This is the 'OLD' Workbook.

[ukphoenix]

Thanx.... ... now I understand what goes where a little better....I needed the Hourly page to stay in the same format so it looks as if your OLD file will do the job.

[rbrhodes]

Great!

As long as you're aware that it's ONLY sorting Col's B & C.

[ukphoenix]

Yes i am aware it only sorts 2 columns (and they are D & E) which is what i wanted as this will be used, before any other information across the board will be entered

Thanx again

[ukphoenix]

Main problem resolved but have a few tinkering queries.

@rbrhodes

In the OLD version of the Hourly worksheet you supplied (many thanx) I need to make some minor adjustments.
The columns the 'Do Sort' button applies to are D & E but I only need the macro to sort asfar as Row 22 as Rows 23 & 24 are assigned for a different reason, hence the different colour.
Could I trouble you to show me what needs to be changed within the macro or sheet for this to work.

In addition if I inserted a new row anywhere within rows 6 - 22 would the macro take this row onboard ?

Warned you I was a novice with macros

[rbrhodes]

Hi,

All you need to do is in the code example below:

[vba]
Option Explicit
Sub Sordid()

'//Runs off of Control ToolBox button

Dim LastRow As Long
Application.EnableEvents = False


'//This line of code starts in B6 and looks down to find the last row
' of data in Column B which (in your example) would be 24. That is
' used in the Sort routine. So to skip the last two rows add a -2 to
' the line as follows:

'(And yes this will include inserted rows!)

'Old line
'LastRow = Range("B6").End(xlDown).Row

'Change to new line:

'New line
LastRow = Range("B6").End(xlDown).Row - 2



'//Sort
Range("C6:E" & LastRow).Sort Key1:=Range("E6"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.EnableEvents = True
End Sub

[/vba]

[ukphoenix]

Thanx again ...cant believe how stupid i am sometimes ... something so simple