I have the following code to sort column b in worksheet1 based on name which
works just fine however when it sorts it does not keep all the cells
together, how do I make it keep the cell together then sort column b?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B5001"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value =
WorksheetFunction.Max(Range("A1:A5001")) + 1
Me.Range("A:G").Sort key1:=Me.Range("B3"), header:=xlYes
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

It sure looks like the rows in columns A:G would be kept together.

Any chance you have formulas like this:
=sheet2!x272
in any of the cells that "don't stay together".

Those formulas won't work the way you want when you sort.

Mekinnik wrote:

I have the following code to sort column b in worksheet1 based on name which
works just fine however when it sorts it does not keep all the cells
together, how do I make it keep the cell together then sort column b?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B5001"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value =
WorksheetFunction.Max(Range("A1:A5001")) + 1
Me.Range("A:G").Sort key1:=Me.Range("B3"), header:=xlYes
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

--

Dave Peterson

Thank you for the reply, but it works just fine the problem is created when I
try to apply the following code, I would like to know how to write it as a
formula to enter into all the cells in one column, what I am trying to do is
have the cell change what the code is entering into it to another value.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 5 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Lists").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Lists").Range("StateName"), 0), 0)
Application.EnableEvents = True
End If
End Sub
"Dave Peterson" wrote:

It sure looks like the rows in columns A:G would be kept together.

Any chance you have formulas like this:
=sheet2!x272
in any of the cells that "don't stay together".

Those formulas won't work the way you want when you sort.

Mekinnik wrote:

I have the following code to sort column b in worksheet1 based on name which
works just fine however when it sorts it does not keep all the cells
together, how do I make it keep the cell together then sort column b?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B5001"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value =
WorksheetFunction.Max(Range("A1:A5001")) + 1
Me.Range("A:G").Sort key1:=Me.Range("B3"), header:=xlYes
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

--

Dave Peterson

Mr. Peterson,
I would like to know if I could e-mail you what I am working on you better
help you understand what my problem is?

"Dave Peterson" wrote:

It sure looks like the rows in columns A:G would be kept together.

Any chance you have formulas like this:
=sheet2!x272
in any of the cells that "don't stay together".

Those formulas won't work the way you want when you sort.

Mekinnik wrote:

I have the following code to sort column b in worksheet1 based on name which
works just fine however when it sorts it does not keep all the cells
together, how do I make it keep the cell together then sort column b?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B5001"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value =
WorksheetFunction.Max(Range("A1:A5001")) + 1
Me.Range("A:G").Sort key1:=Me.Range("B3"), header:=xlYes
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

--

Dave Peterson

Please keep the discussion in the newsgroup. You'll have lots more potential
helpers by keeping it here.

Try explaining what you want in plain text (no attachments) and if anyone can
help, I'm sure they'll jump in.

Mekinnik wrote:

Mr. Peterson,
I would like to know if I could e-mail you what I am working on you better
help you understand what my problem is?

"Dave Peterson" wrote:

It sure looks like the rows in columns A:G would be kept together.

Any chance you have formulas like this:
=sheet2!x272
in any of the cells that "don't stay together".

Those formulas won't work the way you want when you sort.

Mekinnik wrote:

I have the following code to sort column b in worksheet1 based on name which
works just fine however when it sorts it does not keep all the cells
together, how do I make it keep the cell together then sort column b?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B5001"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value =
WorksheetFunction.Max(Range("A1:A5001")) + 1
Me.Range("A:G").Sort key1:=Me.Range("B3"), header:=xlYes
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

--

Dave Peterson


--

Dave Peterson

I have a userform with 5 textboxes and 1 combobox (gets it rowsource from
sheet2 column a) and when the user fills in and selects the state name from
the combobox and then clicks the add button all the information is enter into
sheet1 the next empty row, now two problems with my code.

#1) I want a code or formula that will change the state name when it is
entered into the corresponding column into the state abbreviation (which I
was able to do when I made the column a dropdown box, however I donnot know
how to do it with code.

#2) The code I have listed previously is suppose to sort the manufacturer
name when the worksheet is changed which it does however it will not keep the
other cells in the row together.

I hope I have explained my dilema well enough.

If not please let me know and I will try to elaborate


"Dave Peterson" wrote:

Please keep the discussion in the newsgroup. You'll have lots more potential
helpers by keeping it here.

Try explaining what you want in plain text (no attachments) and if anyone can
help, I'm sure they'll jump in.

Mekinnik wrote:

Mr. Peterson,
I would like to know if I could e-mail you what I am working on you better
help you understand what my problem is?

"Dave Peterson" wrote:

It sure looks like the rows in columns A:G would be kept together.

Any chance you have formulas like this:
=sheet2!x272
in any of the cells that "don't stay together".

Those formulas won't work the way you want when you sort.

Mekinnik wrote:

I have the following code to sort column b in worksheet1 based on name which
works just fine however when it sorts it does not keep all the cells
together, how do I make it keep the cell together then sort column b?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B5001"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value =
WorksheetFunction.Max(Range("A1:A5001")) + 1
Me.Range("A:G").Sort key1:=Me.Range("B3"), header:=xlYes
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

--

Dave Peterson


--

Dave Peterson

#1. In the code that you use to add the data from the userform into the
worksheet:

Dim res as variant
dim StateName as String
res = application.vlookup(me.textbox99.value, _
worksheets("Sheet999").range("a:b"), 2, false)
if iserror(res) then
statename = "Not found"
else
statename = res
end if
And put Statename in the cell that gets the spelled out state.

I used textbox99 to hold the abbreviation for the state.
Sheet999 held a table with the abbreviations in column A and the long name in
column B.

#2. If you're updating the worksheet through the userform, then don't rely on
the worksheet_change to sort your data--just sort it after you finish populating
the worksheet (with the userform code).

If you really, really want to sort the data with the worksheet_change event,
then I bet you're changing something that causes the event fire prematurely
(updating the worksheet cell by cell).

Turn off events
update all the cells except column B.
then on events and update the value in column B.
Then your worksheet event will notice the change in column B and sort everything
ok.


with worksheets("somesheethere")
application.enableevents = false
.cells(somerow,"A").value = me.textbox1.value
.cells(somerow,"C").value = me.textbox88.value
.cells(somerow,"D").value = statename
.cells(somerow,"E").value = me.textbox11.value
application.enableevents = true
'the sort will fire with this line.
.cells(somerow,"B").value = me.textbox9999.value
end with



Mekinnik wrote:

I have a userform with 5 textboxes and 1 combobox (gets it rowsource from
sheet2 column a) and when the user fills in and selects the state name from
the combobox and then clicks the add button all the information is enter into
sheet1 the next empty row, now two problems with my code.

#1) I want a code or formula that will change the state name when it is
entered into the corresponding column into the state abbreviation (which I
was able to do when I made the column a dropdown box, however I donnot know
how to do it with code.

#2) The code I have listed previously is suppose to sort the manufacturer
name when the worksheet is changed which it does however it will not keep the
other cells in the row together.

I hope I have explained my dilema well enough.

If not please let me know and I will try to elaborate


"Dave Peterson" wrote:

Please keep the discussion in the newsgroup. You'll have lots more potential
helpers by keeping it here.

Try explaining what you want in plain text (no attachments) and if anyone can
help, I'm sure they'll jump in.

Mekinnik wrote:

Mr. Peterson,
I would like to know if I could e-mail you what I am working on you better
help you understand what my problem is?

"Dave Peterson" wrote:

It sure looks like the rows in columns A:G would be kept together.

Any chance you have formulas like this:
=sheet2!x272
in any of the cells that "don't stay together".

Those formulas won't work the way you want when you sort.

Mekinnik wrote:

I have the following code to sort column b in worksheet1 based on name which
works just fine however when it sorts it does not keep all the cells
together, how do I make it keep the cell together then sort column b?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B5001"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value =
WorksheetFunction.Max(Range("A1:A5001")) + 1
Me.Range("A:G").Sort key1:=Me.Range("B3"), header:=xlYes
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson