Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting issue??
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting issue??
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting issue??
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting issue??
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting issue??
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting issue??
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting issue??
#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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting issue??
Mr. Peterson I followed what you posted but it doesn't work still maybe I'm
doing some thing wrong. I have the worksheets 'MANCODE' where the userform is adding info, 'Lists' where I have column A the state abbreviations and cloumn B the full stste names. Here is what my code looks like for the 'ADD' button click event. Private Sub BtnAdd_Click() Dim iRow As Long Dim ws As Worksheet Dim res As Variant Dim StateName As String res = Application.VLookup(Me.CmbSt.Value, _ Worksheets("Lists").Range("A:B"), 2, False) Set ws = Worksheets("MANCODE") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for the manufacturer name If Trim(Me.TxtMan.Value) = "" Then Me.TxtMan.SetFocus MsgBox "Please enter the Manufacturer's name" Exit Sub End If If IsError(res) Then StateName = "Not Found" Else StateName = res End If 'copy the data to the database Application.EnableEvents = False ws.Cells(iRow, 3).Value = Me.TxtAdd.Value ws.Cells(iRow, 4).Value = Me.TxtCity.Value ws.Cells(iRow, 5).Value = Me.CmbSt.Value ws.Cells(iRow, 6).Value = Me.TxtZip.Value ws.Cells(iRow, 7).Value = Me.TxtPhn.Value Application.EnableEvents = True 'the sort will fire with this line. ws.Cells(iRow, 2).Value = Me.TxtMan.Value 'clear the data Me.TxtMan.Value = "" Me.TxtAdd.Value = "" Me.TxtCity.Value = "" Me.CmbSt.Value = "" Me.TxtZip.Value = "" Me.TxtPhn.Value = "" End Sub I believe after rereading your post again that it is backwards I want when the user selects the full state name from 'CmbSt' combobox it will return the state abbreviation back to 'MANCODE' column E and with the code the way it is column E gets the full state name not the abbreviation. "Dave Peterson" wrote: #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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting issue??
So the only part that doesn't work is the abbreviation/long state name?
with worksheets("lists") res = application.match(me.cmbst.value,.range("B:b",0) if iserror(res) then 'no match found! else statename = .range("A:a")(res) end if end with if there was more that didn't work, you're going to have to describe that part. Mekinnik wrote: Mr. Peterson I followed what you posted but it doesn't work still maybe I'm doing some thing wrong. I have the worksheets 'MANCODE' where the userform is adding info, 'Lists' where I have column A the state abbreviations and cloumn B the full stste names. Here is what my code looks like for the 'ADD' button click event. Private Sub BtnAdd_Click() Dim iRow As Long Dim ws As Worksheet Dim res As Variant Dim StateName As String res = Application.VLookup(Me.CmbSt.Value, _ Worksheets("Lists").Range("A:B"), 2, False) Set ws = Worksheets("MANCODE") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for the manufacturer name If Trim(Me.TxtMan.Value) = "" Then Me.TxtMan.SetFocus MsgBox "Please enter the Manufacturer's name" Exit Sub End If If IsError(res) Then StateName = "Not Found" Else StateName = res End If 'copy the data to the database Application.EnableEvents = False ws.Cells(iRow, 3).Value = Me.TxtAdd.Value ws.Cells(iRow, 4).Value = Me.TxtCity.Value ws.Cells(iRow, 5).Value = Me.CmbSt.Value ws.Cells(iRow, 6).Value = Me.TxtZip.Value ws.Cells(iRow, 7).Value = Me.TxtPhn.Value Application.EnableEvents = True 'the sort will fire with this line. ws.Cells(iRow, 2).Value = Me.TxtMan.Value 'clear the data Me.TxtMan.Value = "" Me.TxtAdd.Value = "" Me.TxtCity.Value = "" Me.CmbSt.Value = "" Me.TxtZip.Value = "" Me.TxtPhn.Value = "" End Sub I believe after rereading your post again that it is backwards I want when the user selects the full state name from 'CmbSt' combobox it will return the state abbreviation back to 'MANCODE' column E and with the code the way it is column E gets the full state name not the abbreviation. "Dave Peterson" wrote: #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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting issue??
I tried the code below and it didn't work, then I tried it differently and it
still didn't work, any suggestions? Private Sub BtnAdd_Click() Dim iRow As Long Dim ws As Worksheet Dim res As Variant Dim StateName As String Set ws = Worksheets("MANCODE") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for the manufacturer name If Trim(Me.TxtMan.Value) = "" Then Me.TxtMan.SetFocus MsgBox "Please enter the Manufacturer's name" Exit Sub End If With Worksheets("Lists") res = Application.VLookup(Me.CmbSt.Value, _ Worksheets("Lists").Range("A:B"), 2, False) 'res = Application.Match(Me.CmbSt.Value, .Range("B:b"), 0) 'If IsError(res) Then 'StateName = "Not Found" StateName = .Range("A:a")(res) End With 'copy the data to the database Application.EnableEvents = False ws.Cells(iRow, 3).Value = Me.TxtAdd.Value ws.Cells(iRow, 4).Value = Me.TxtCity.Value ws.Cells(iRow, 5).Value = Me.CmbSt.Value ws.Cells(iRow, 6).Value = Me.TxtZip.Value ws.Cells(iRow, 7).Value = Me.TxtPhn.Value Application.EnableEvents = True 'the sort will fire with this line. ws.Cells(iRow, 2).Value = Me.TxtMan.Value 'clear the data Me.TxtMan.Value = "" Me.TxtAdd.Value = "" Me.TxtCity.Value = "" Me.CmbSt.Value = "" Me.TxtZip.Value = "" Me.TxtPhn.Value = "" End Sub "Dave Peterson" wrote: So the only part that doesn't work is the abbreviation/long state name? with worksheets("lists") res = application.match(me.cmbst.value,.range("B:b",0) if iserror(res) then 'no match found! else statename = .range("A:a")(res) end if end with if there was more that didn't work, you're going to have to describe that part. Mekinnik wrote: Mr. Peterson I followed what you posted but it doesn't work still maybe I'm doing some thing wrong. I have the worksheets 'MANCODE' where the userform is adding info, 'Lists' where I have column A the state abbreviations and cloumn B the full stste names. Here is what my code looks like for the 'ADD' button click event. Private Sub BtnAdd_Click() Dim iRow As Long Dim ws As Worksheet Dim res As Variant Dim StateName As String res = Application.VLookup(Me.CmbSt.Value, _ Worksheets("Lists").Range("A:B"), 2, False) Set ws = Worksheets("MANCODE") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for the manufacturer name If Trim(Me.TxtMan.Value) = "" Then Me.TxtMan.SetFocus MsgBox "Please enter the Manufacturer's name" Exit Sub End If If IsError(res) Then StateName = "Not Found" Else StateName = res End If 'copy the data to the database Application.EnableEvents = False ws.Cells(iRow, 3).Value = Me.TxtAdd.Value ws.Cells(iRow, 4).Value = Me.TxtCity.Value ws.Cells(iRow, 5).Value = Me.CmbSt.Value ws.Cells(iRow, 6).Value = Me.TxtZip.Value ws.Cells(iRow, 7).Value = Me.TxtPhn.Value Application.EnableEvents = True 'the sort will fire with this line. ws.Cells(iRow, 2).Value = Me.TxtMan.Value 'clear the data Me.TxtMan.Value = "" Me.TxtAdd.Value = "" Me.TxtCity.Value = "" Me.CmbSt.Value = "" Me.TxtZip.Value = "" Me.TxtPhn.Value = "" End Sub I believe after rereading your post again that it is backwards I want when the user selects the full state name from 'CmbSt' combobox it will return the state abbreviation back to 'MANCODE' column E and with the code the way it is column E gets the full state name not the abbreviation. "Dave Peterson" wrote: #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 -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting issue??
I managed to get it to work with some tweaking here is the modified code,
thank you for all your help. Private Sub BtnAdd_Click() Dim iRow As Long Dim ws As Worksheet Dim res As Variant Set ws = Worksheets("MANCODE") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for the manufacturer name If Trim(Me.TxtMan.Value) = "" Then Me.TxtMan.SetFocus MsgBox "Please enter the Manufacturer's name" Exit Sub End If 'find and copy state abbreviation to row 5 With Worksheets("Lists") res = Application.VLookup(Me.CmbSt.Value, _ Worksheets("Lists").Range("A:B"), 2, False) If IsError(res) Then Else ws.Cells(iRow, 5).Value = (res) End If End With 'copy the data to the database Application.EnableEvents = False ws.Cells(iRow, 3).Value = Me.TxtAdd.Value ws.Cells(iRow, 4).Value = Me.TxtCity.Value ws.Cells(iRow, 6).Value = Me.TxtZip.Value ws.Cells(iRow, 7).Value = Me.TxtPhn.Value Application.EnableEvents = True 'the sort will fire with this line. ws.Cells(iRow, 2).Value = Me.TxtMan.Value 'clear the data Me.TxtMan.Value = "" Me.TxtAdd.Value = "" Me.TxtCity.Value = "" Me.CmbSt.Value = "" Me.TxtZip.Value = "" Me.TxtPhn.Value = "" End Sub "Dave Peterson" wrote: So the only part that doesn't work is the abbreviation/long state name? with worksheets("lists") res = application.match(me.cmbst.value,.range("B:b",0) if iserror(res) then 'no match found! else statename = .range("A:a")(res) end if end with if there was more that didn't work, you're going to have to describe that part. Mekinnik wrote: Mr. Peterson I followed what you posted but it doesn't work still maybe I'm doing some thing wrong. I have the worksheets 'MANCODE' where the userform is adding info, 'Lists' where I have column A the state abbreviations and cloumn B the full stste names. Here is what my code looks like for the 'ADD' button click event. Private Sub BtnAdd_Click() Dim iRow As Long Dim ws As Worksheet Dim res As Variant Dim StateName As String res = Application.VLookup(Me.CmbSt.Value, _ Worksheets("Lists").Range("A:B"), 2, False) Set ws = Worksheets("MANCODE") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for the manufacturer name If Trim(Me.TxtMan.Value) = "" Then Me.TxtMan.SetFocus MsgBox "Please enter the Manufacturer's name" Exit Sub End If If IsError(res) Then StateName = "Not Found" Else StateName = res End If 'copy the data to the database Application.EnableEvents = False ws.Cells(iRow, 3).Value = Me.TxtAdd.Value ws.Cells(iRow, 4).Value = Me.TxtCity.Value ws.Cells(iRow, 5).Value = Me.CmbSt.Value ws.Cells(iRow, 6).Value = Me.TxtZip.Value ws.Cells(iRow, 7).Value = Me.TxtPhn.Value Application.EnableEvents = True 'the sort will fire with this line. ws.Cells(iRow, 2).Value = Me.TxtMan.Value 'clear the data Me.TxtMan.Value = "" Me.TxtAdd.Value = "" Me.TxtCity.Value = "" Me.CmbSt.Value = "" Me.TxtZip.Value = "" Me.TxtPhn.Value = "" End Sub I believe after rereading your post again that it is backwards I want when the user selects the full state name from 'CmbSt' combobox it will return the state abbreviation back to 'MANCODE' column E and with the code the way it is column E gets the full state name not the abbreviation. "Dave Peterson" wrote: #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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting issue | Excel Worksheet Functions | |||
sorting issue | Excel Discussion (Misc queries) | |||
Sorting issue | Excel Programming | |||
Sorting Issue. Please help | Excel Discussion (Misc queries) | |||
sorting issue - help | Excel Discussion (Misc queries) |