Help Please... 5

[JrClown]

hello VBA experts, I need your help today fellas.

I have an Excel sheet (form) as follows:

Check box Name Project TimeIn TimeDue DataTech

When user have entered 20 entries or so, the 21st entry may be a priority so they check the checkbox. I would like to know how to make this record move up when the box is checked. Any help would be appreciated.

I would also like to know how to sort all entries by timedue field. QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[DarkSun]

Could you give more details how your Form is designed.

where is the data stored? (excel sheet or listbox)

If you email me the file I could do what you want.

dark_suns@lycos.com

 

[JrClown]

a Test sheet has just been emailed. thanks QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[DarkSun]

I have received it. I will have a look tonight.

 

[JrClown]

Thanks, do not hesitate with any questions please. QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[LoNeRaVeR]

DarkSun:

I'm gonna hang on to that E-mail address. s-) One can never tell when it may come in handy.

How 'bout postin' the code, or at least some of it after you finish helping JrClown?

Thanks,

LoNeRaVeR

 

[JrClown]

DarkSun is the best at things like this LoNeRaVeR

QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[LoNeRaVeR]

JrClown:

Hummm... I take that as a challenge... How 'bout ya send a copy of what you have to Excel@LoNeRaVeR.com and I'll take a look, too.

LoNeRaVeR

 

[JrClown]

You should be getting it in about 2 minutes QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[pi]

DarkSun:

How about posting the code and solution on this forum.
I am learning to to code and have found this forum to be my best teacher.
I look forward to reading the posts and generally try to understand the various code with solutions- not always successful though.

Thank you for your indulgence.

Regards

Pi (Dasi Govender)

 

[LoNeRaVeR]

JrClown:

Challenge accepted! Copy the following code into the Worksheet Module of the Workbook where you have the users entering the data.
[tt]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.ScreenUpdating = False
sRange = ActiveCell.Address
Selection.CurrentRegion.Select
Selection.Sort _
Key1:=Range(&quot;I2&quot, _
Order1:=xlDescending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range(sRange).Select
Application.ScreenUpdating = True
End If
End Sub
[/tt]
Some notes:
1) The Worksheet Module is the one listed
under Microsoft Excel Objects. An example
would be Sheet1 (Sheet1).
2) Set [tt]Target.Column[/tt] to the column where
you would like to check for the change.
3) Set [tt]Key1:=Range(&quot;I2&quot[/tt] to the letter
of the column you would like to sort.

Please let me know if you have any problems.
[tt]
LoNeRaVeR
[/tt]

 

[LoNeRaVeR]

JrClown:

One more note about the code. If you enter a blank column between column B and column I, you will receive a

Run-Time error '1004':

The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.

This can be avoided with a slightly different approach when selecting the range of cells to sort rather than using:
[tt]
Selection.CurrentRegion.Select
[/tt]
It works very well with the example you sent, though.

Regards,

LoNeRaVeR

 

[DarkSun]

Jr Clown,
I have sent you an email, have a read then get back to me.

Cheers,
DarkSun

 

[JrClown]

Have yet to receive it. thanks QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[JrClown]

Ok here we go fellas.

LoNeRaVeR.
Your code worked, but is not sorting input data by TimeDUE (Perhaps I'm missing something)

DarkSun.
Your sample work great, I would just need to make some modification and work with it. Tho I may need some help when editing an existing record. As I mention before, not all users have Access or I would use it. If you have an idea how access can be used in this case, shoot by all means. Thanks fellas

QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[LoNeRaVeR]

Jr Clown:

I replied to your E-mail with my example included. I changed the range from
[tt]
Selection.CurrentRegion.Select
[/tt]
to include the range of cells in your example. In the example you sent via E-mail the TimeDue field is in column I, so that's the one that is sorted. Unfortunately, if there was data missing in any of the columns it would generate an error. The following is an update to my original post. Adjust the [tt]sRange[/tt] string variable to include the range you would like sorted. Change the [tt]Target.Column = 2[/tt] (Column B) to the Column number you would like to check for an entry.
[tt]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sCell As String
Dim sRange As String
If Target.Column = 2 Then
Application.ScreenUpdating = False
sCell = ActiveCell.Address
sRange = &quot;A1:K98&quot;
Range(sRange).Sort _
Key1:=Range(&quot;I2&quot, _
Order1:=xlDescending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range(sCell).Select
Application.ScreenUpdating = True
End If
End Sub
[/tt]
A few more notes:
1) This example will only work when entering data into Column B.
2) This example sorts by Column I.
3) The range to sort is set to &quot;A1:K98&quot;.
4) Remember that this will only work when this code is placed in the
Module for the Sheet Object where you want to detect the change.

If anyone has any questions about how to make this work, adapt it to your needs, or would like a working example sent via E-mail please let me know, and I'll be glad to help.

Cheers,

LoNeRaVeR

 

[JrClown]

LoNeRaVeR, I've copied the code just as you instructed me, and have entered data into it, but it's not sorting at all? Need a change something else???
X-) s-) QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[LoNeRaVeR]

Jr Clown:

Take a gander at the spreadsheet I just sent to you. The code is included for both your requests.

LoNeRaVeR

 

[JrClown]

ok Thank you Sir, I will look at it when received. QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[JrClown]

LoNeRaVeR
I never received the email Sir. Please send it again. Thank you very much QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>