|
Help with Forms
Hi,
I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
Try Somthig like this
Option Explicit Sub CommandButton1_Click() Range("A2").Value = ComboBox1.Value '<-- You Can change you ranges as needed If ComboBox1.Value = "Marchwood" Then Range("C2").Value = TextBox1.Value ElseIf ComboBox1.Value = "Other Companies" Then Range("C2").Value = TextBox1.Value End If End Sub Private Sub UserForm_Initialize() ComboBox1.AddItem "Marchwood" ComboBox1.AddItem "Other Companies" End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
You may find that using data|Form does what you want even better.
(xl2003 menu) But if the value in the combobox (and column A) is unique, you could use something like: Option Explicit Private Sub CommandButton1_Click() Dim myRng As Range Dim Res As Variant 'could be an error With Worksheets("sheet999") Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With Res = Application.Match(Me.ComboBox1.Value, myRng, 0) If IsError(Res) Then 'no match, what should happen Else with myRng(Res).Offset(0, 2) .numberformat = "mm/dd/yyyy" .Value = Me.TextBox1.Value end with End If End Sub If you're entering dates, you may find problems with the textbox: Does 01/02/03 mean Jan 1, 2003 or Feb 1, 2003 or something else??? Ron de Bruin has some notes that describe how to use a calendar control (avoiding any ambiguous entry!) he http://www.rondebruin.nl/calendar.htm Leanne wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks -- Dave Peterson |
Help with Forms
Thanks for this information both of you - I cant test either of them because
I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
Try somthing like this
You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
Hi, Yes that works thankyou very much. Now would you belive that I can not
clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
Put this between end with and end sub
TextBox1.Value = "" I noticed after the post Sry. Glad i could help ;o) "Leanne" wrote: Hi, Yes that works thankyou very much. Now would you belive that I can not clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
No dont be sorry for being cleaver - I think I should change my screen name
to include novice. I have entered this - would you be able to see if I have missed something obvious - I have this entered into the code for a multipage box so have repeated it for the other entry 'Invoice' tab. Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub Thanks again "Office_Novice" wrote: Put this between end with and end sub TextBox1.Value = "" I noticed after the post Sry. Glad i could help ;o) "Leanne" wrote: Hi, Yes that works thankyou very much. Now would you belive that I can not clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
What is the Trouble?
"Leanne" wrote: No dont be sorry for being cleaver - I think I should change my screen name to include novice. I have entered this - would you be able to see if I have missed something obvious - I have this entered into the code for a multipage box so have repeated it for the other entry 'Invoice' tab. Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub Thanks again "Office_Novice" wrote: Put this between end with and end sub TextBox1.Value = "" I noticed after the post Sry. Glad i could help ;o) "Leanne" wrote: Hi, Yes that works thankyou very much. Now would you belive that I can not clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
Opps - are you not a mind reader haha
When I click the Save Entry button on the form it clears the data but does not insert it into the cells. With the clear data command out it does insert it but inserts the date on the tab for customer selected on both tabs. "Office_Novice" wrote: What is the Trouble? "Leanne" wrote: No dont be sorry for being cleaver - I think I should change my screen name to include novice. I have entered this - would you be able to see if I have missed something obvious - I have this entered into the code for a multipage box so have repeated it for the other entry 'Invoice' tab. Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub Thanks again "Office_Novice" wrote: Put this between end with and end sub TextBox1.Value = "" I noticed after the post Sry. Glad i could help ;o) "Leanne" wrote: Hi, Yes that works thankyou very much. Now would you belive that I can not clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
You said you were using a mutipage control. I assume we are talking about the
'Invoice' Tab where are you trying to send this data? please provide Sheet names and cell addresses so may be we can cut down on editing. "Leanne" wrote: Opps - are you not a mind reader haha When I click the Save Entry button on the form it clears the data but does not insert it into the cells. With the clear data command out it does insert it but inserts the date on the tab for customer selected on both tabs. "Office_Novice" wrote: What is the Trouble? "Leanne" wrote: No dont be sorry for being cleaver - I think I should change my screen name to include novice. I have entered this - would you be able to see if I have missed something obvious - I have this entered into the code for a multipage box so have repeated it for the other entry 'Invoice' tab. Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub Thanks again "Office_Novice" wrote: Put this between end with and end sub TextBox1.Value = "" I noticed after the post Sry. Glad i could help ;o) "Leanne" wrote: Hi, Yes that works thankyou very much. Now would you belive that I can not clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
This is exatly how my code appears.
The form has two tabs - one called Visit dates and the other Invoice dates. On each tab there is a combo box called VisitList (InvoiceList) and a text box called VisitDate (InvoiceDate) also a Save Entry button called SaveVisit (SaveInvoice) and an Exit form button called CloseVisit (CloseInvoice) All the information comes and goes to one sheet called Dates and it looks like this ColA ColB ColC Row1 Customer Name Invoice Date Visit Date Row2 Marchwood ERF Row3 Portsmouth ERF I hope I have provided enough information for you and I really appreciate the time you are taking to help me with this. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Sub SaveInvoice_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=InvoiceList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 21).Value = InvoiceDate.Value End If End With InvoiceList.Value = "" InvoiceDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Private Sub UserForm_Initialize() VisitList.RowSource = "Dates!A2:A300" InvoiceList.RowSource = "Dates!A2:A300" End Sub 'Your ComboBox will then be able to grow as your list grows 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub CloseInvoice_Click() Unload Me End Sub Private Sub CloseVisit_Click() Unload Me End Sub "Office_Novice" wrote: You said you were using a mutipage control. I assume we are talking about the 'Invoice' Tab where are you trying to send this data? please provide Sheet names and cell addresses so may be we can cut down on editing. "Leanne" wrote: Opps - are you not a mind reader haha When I click the Save Entry button on the form it clears the data but does not insert it into the cells. With the clear data command out it does insert it but inserts the date on the tab for customer selected on both tabs. "Office_Novice" wrote: What is the Trouble? "Leanne" wrote: No dont be sorry for being cleaver - I think I should change my screen name to include novice. I have entered this - would you be able to see if I have missed something obvious - I have this entered into the code for a multipage box so have repeated it for the other entry 'Invoice' tab. Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub Thanks again "Office_Novice" wrote: Put this between end with and end sub TextBox1.Value = "" I noticed after the post Sry. Glad i could help ;o) "Leanne" wrote: Hi, Yes that works thankyou very much. Now would you belive that I can not clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
no problem, I have been there. Some of these guys really know their stuff.
Try this ... Private Sub SaveInvoice_Click() Dim foundCell As Range With Worksheets(1).Range("a1:a65536") Set foundCell = .Find(What:=Invoicelist.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundCell Is Nothing Then foundCell.Offset(0, 1).Value = InvoiceDate.Value End If End With Invoicelist.Value = "" InvoiceDate.Value = "" End Sub "Leanne" wrote: This is exatly how my code appears. The form has two tabs - one called Visit dates and the other Invoice dates. On each tab there is a combo box called VisitList (InvoiceList) and a text box called VisitDate (InvoiceDate) also a Save Entry button called SaveVisit (SaveInvoice) and an Exit form button called CloseVisit (CloseInvoice) All the information comes and goes to one sheet called Dates and it looks like this ColA ColB ColC Row1 Customer Name Invoice Date Visit Date Row2 Marchwood ERF Row3 Portsmouth ERF I hope I have provided enough information for you and I really appreciate the time you are taking to help me with this. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Sub SaveInvoice_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=InvoiceList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 21).Value = InvoiceDate.Value End If End With InvoiceList.Value = "" InvoiceDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Private Sub UserForm_Initialize() VisitList.RowSource = "Dates!A2:A300" InvoiceList.RowSource = "Dates!A2:A300" End Sub 'Your ComboBox will then be able to grow as your list grows 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub CloseInvoice_Click() Unload Me End Sub Private Sub CloseVisit_Click() Unload Me End Sub "Office_Novice" wrote: You said you were using a mutipage control. I assume we are talking about the 'Invoice' Tab where are you trying to send this data? please provide Sheet names and cell addresses so may be we can cut down on editing. "Leanne" wrote: Opps - are you not a mind reader haha When I click the Save Entry button on the form it clears the data but does not insert it into the cells. With the clear data command out it does insert it but inserts the date on the tab for customer selected on both tabs. "Office_Novice" wrote: What is the Trouble? "Leanne" wrote: No dont be sorry for being cleaver - I think I should change my screen name to include novice. I have entered this - would you be able to see if I have missed something obvious - I have this entered into the code for a multipage box so have repeated it for the other entry 'Invoice' tab. Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub Thanks again "Office_Novice" wrote: Put this between end with and end sub TextBox1.Value = "" I noticed after the post Sry. Glad i could help ;o) "Leanne" wrote: Hi, Yes that works thankyou very much. Now would you belive that I can not clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
Also
Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" Invoicelist.RowSource = "Sheet1!A2:A30" End Sub "Leanne" wrote: This is exatly how my code appears. The form has two tabs - one called Visit dates and the other Invoice dates. On each tab there is a combo box called VisitList (InvoiceList) and a text box called VisitDate (InvoiceDate) also a Save Entry button called SaveVisit (SaveInvoice) and an Exit form button called CloseVisit (CloseInvoice) All the information comes and goes to one sheet called Dates and it looks like this ColA ColB ColC Row1 Customer Name Invoice Date Visit Date Row2 Marchwood ERF Row3 Portsmouth ERF I hope I have provided enough information for you and I really appreciate the time you are taking to help me with this. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Sub SaveInvoice_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=InvoiceList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 21).Value = InvoiceDate.Value End If End With InvoiceList.Value = "" InvoiceDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Private Sub UserForm_Initialize() VisitList.RowSource = "Dates!A2:A300" InvoiceList.RowSource = "Dates!A2:A300" End Sub 'Your ComboBox will then be able to grow as your list grows 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub CloseInvoice_Click() Unload Me End Sub Private Sub CloseVisit_Click() Unload Me End Sub "Office_Novice" wrote: You said you were using a mutipage control. I assume we are talking about the 'Invoice' Tab where are you trying to send this data? please provide Sheet names and cell addresses so may be we can cut down on editing. "Leanne" wrote: Opps - are you not a mind reader haha When I click the Save Entry button on the form it clears the data but does not insert it into the cells. With the clear data command out it does insert it but inserts the date on the tab for customer selected on both tabs. "Office_Novice" wrote: What is the Trouble? "Leanne" wrote: No dont be sorry for being cleaver - I think I should change my screen name to include novice. I have entered this - would you be able to see if I have missed something obvious - I have this entered into the code for a multipage box so have repeated it for the other entry 'Invoice' tab. Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub Thanks again "Office_Novice" wrote: Put this between end with and end sub TextBox1.Value = "" I noticed after the post Sry. Glad i could help ;o) "Leanne" wrote: Hi, Yes that works thankyou very much. Now would you belive that I can not clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
It still does not work, it is clearing the data from the form but not
entering it in the sheet. I have had my fill of it for today and will try to come back tomorrow with a fresh mind. Thanks agiain for your help. You are most certainly right about them knowing their stuff - only trouble is I have difficulty understanding what they mean! I will get there sometime I guess. "Office_Novice" wrote: no problem, I have been there. Some of these guys really know their stuff. Try this ... Private Sub SaveInvoice_Click() Dim foundCell As Range With Worksheets(1).Range("a1:a65536") Set foundCell = .Find(What:=Invoicelist.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundCell Is Nothing Then foundCell.Offset(0, 1).Value = InvoiceDate.Value End If End With Invoicelist.Value = "" InvoiceDate.Value = "" End Sub "Leanne" wrote: This is exatly how my code appears. The form has two tabs - one called Visit dates and the other Invoice dates. On each tab there is a combo box called VisitList (InvoiceList) and a text box called VisitDate (InvoiceDate) also a Save Entry button called SaveVisit (SaveInvoice) and an Exit form button called CloseVisit (CloseInvoice) All the information comes and goes to one sheet called Dates and it looks like this ColA ColB ColC Row1 Customer Name Invoice Date Visit Date Row2 Marchwood ERF Row3 Portsmouth ERF I hope I have provided enough information for you and I really appreciate the time you are taking to help me with this. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Sub SaveInvoice_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=InvoiceList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 21).Value = InvoiceDate.Value End If End With InvoiceList.Value = "" InvoiceDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Private Sub UserForm_Initialize() VisitList.RowSource = "Dates!A2:A300" InvoiceList.RowSource = "Dates!A2:A300" End Sub 'Your ComboBox will then be able to grow as your list grows 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub CloseInvoice_Click() Unload Me End Sub Private Sub CloseVisit_Click() Unload Me End Sub "Office_Novice" wrote: You said you were using a mutipage control. I assume we are talking about the 'Invoice' Tab where are you trying to send this data? please provide Sheet names and cell addresses so may be we can cut down on editing. "Leanne" wrote: Opps - are you not a mind reader haha When I click the Save Entry button on the form it clears the data but does not insert it into the cells. With the clear data command out it does insert it but inserts the date on the tab for customer selected on both tabs. "Office_Novice" wrote: What is the Trouble? "Leanne" wrote: No dont be sorry for being cleaver - I think I should change my screen name to include novice. I have entered this - would you be able to see if I have missed something obvious - I have this entered into the code for a multipage box so have repeated it for the other entry 'Invoice' tab. Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub Thanks again "Office_Novice" wrote: Put this between end with and end sub TextBox1.Value = "" I noticed after the post Sry. Glad i could help ;o) "Leanne" wrote: Hi, Yes that works thankyou very much. Now would you belive that I can not clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
Here is what i have good luck. I hope this helps.
Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundCell As Range With Worksheets(1).Range("A1:A65536") Set foundCell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundCell Is Nothing Then foundCell.Offset(0, 2).Value = TextBox1.Value End If End With TextBox1.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Private Sub CommandButton2_Click() Dim foundCell As Range With Worksheets(1).Range("a1:a65536") Set foundCell = .Find(What:=ComboBox2.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundCell Is Nothing Then foundCell.Offset(0, 1).Value = TextBox2.Value End If End With TextBox1.Value = "" End Sub Private Sub CommandButton3_Click() Unload Me End Sub Private Sub CommandButton4_Click() Unload Me End Sub 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" ComboBox2.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Office_Novice" wrote: Also Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" Invoicelist.RowSource = "Sheet1!A2:A30" End Sub "Leanne" wrote: This is exatly how my code appears. The form has two tabs - one called Visit dates and the other Invoice dates. On each tab there is a combo box called VisitList (InvoiceList) and a text box called VisitDate (InvoiceDate) also a Save Entry button called SaveVisit (SaveInvoice) and an Exit form button called CloseVisit (CloseInvoice) All the information comes and goes to one sheet called Dates and it looks like this ColA ColB ColC Row1 Customer Name Invoice Date Visit Date Row2 Marchwood ERF Row3 Portsmouth ERF I hope I have provided enough information for you and I really appreciate the time you are taking to help me with this. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Sub SaveInvoice_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=InvoiceList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 21).Value = InvoiceDate.Value End If End With InvoiceList.Value = "" InvoiceDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Private Sub UserForm_Initialize() VisitList.RowSource = "Dates!A2:A300" InvoiceList.RowSource = "Dates!A2:A300" End Sub 'Your ComboBox will then be able to grow as your list grows 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub CloseInvoice_Click() Unload Me End Sub Private Sub CloseVisit_Click() Unload Me End Sub "Office_Novice" wrote: You said you were using a mutipage control. I assume we are talking about the 'Invoice' Tab where are you trying to send this data? please provide Sheet names and cell addresses so may be we can cut down on editing. "Leanne" wrote: Opps - are you not a mind reader haha When I click the Save Entry button on the form it clears the data but does not insert it into the cells. With the clear data command out it does insert it but inserts the date on the tab for customer selected on both tabs. "Office_Novice" wrote: What is the Trouble? "Leanne" wrote: No dont be sorry for being cleaver - I think I should change my screen name to include novice. I have entered this - would you be able to see if I have missed something obvious - I have this entered into the code for a multipage box so have repeated it for the other entry 'Invoice' tab. Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub Thanks again "Office_Novice" wrote: Put this between end with and end sub TextBox1.Value = "" I noticed after the post Sry. Glad i could help ;o) "Leanne" wrote: Hi, Yes that works thankyou very much. Now would you belive that I can not clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct manner. If someone selects Marchwood from the combo box and enters 01/05/08 in the text box I want the record for Marchwood to be updated. Marchwood is in A2 and the data would need to update in C2 everytime Marchwood is chosen - and the same for all the other companies. Can anyone offer any suggestions? Thanks |
Help with Forms
Thanks for your time - I can't belive I am saying this but I think it has
defeated me. I have put this code in and changed it as necessary but still no joy. It clears the boxes fine but does not enter the data anywhere. At this stage I can not even write the code for a simple form anymore so I think I need to leave it be. I will just add a link to the sheet and the user will have to filter the list and enter the date manually. "Office_Novice" wrote: Here is what i have good luck. I hope this helps. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundCell As Range With Worksheets(1).Range("A1:A65536") Set foundCell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundCell Is Nothing Then foundCell.Offset(0, 2).Value = TextBox1.Value End If End With TextBox1.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Private Sub CommandButton2_Click() Dim foundCell As Range With Worksheets(1).Range("a1:a65536") Set foundCell = .Find(What:=ComboBox2.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundCell Is Nothing Then foundCell.Offset(0, 1).Value = TextBox2.Value End If End With TextBox1.Value = "" End Sub Private Sub CommandButton3_Click() Unload Me End Sub Private Sub CommandButton4_Click() Unload Me End Sub 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" ComboBox2.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Office_Novice" wrote: Also Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" Invoicelist.RowSource = "Sheet1!A2:A30" End Sub "Leanne" wrote: This is exatly how my code appears. The form has two tabs - one called Visit dates and the other Invoice dates. On each tab there is a combo box called VisitList (InvoiceList) and a text box called VisitDate (InvoiceDate) also a Save Entry button called SaveVisit (SaveInvoice) and an Exit form button called CloseVisit (CloseInvoice) All the information comes and goes to one sheet called Dates and it looks like this ColA ColB ColC Row1 Customer Name Invoice Date Visit Date Row2 Marchwood ERF Row3 Portsmouth ERF I hope I have provided enough information for you and I really appreciate the time you are taking to help me with this. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Sub SaveInvoice_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=InvoiceList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 21).Value = InvoiceDate.Value End If End With InvoiceList.Value = "" InvoiceDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Private Sub UserForm_Initialize() VisitList.RowSource = "Dates!A2:A300" InvoiceList.RowSource = "Dates!A2:A300" End Sub 'Your ComboBox will then be able to grow as your list grows 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub CloseInvoice_Click() Unload Me End Sub Private Sub CloseVisit_Click() Unload Me End Sub "Office_Novice" wrote: You said you were using a mutipage control. I assume we are talking about the 'Invoice' Tab where are you trying to send this data? please provide Sheet names and cell addresses so may be we can cut down on editing. "Leanne" wrote: Opps - are you not a mind reader haha When I click the Save Entry button on the form it clears the data but does not insert it into the cells. With the clear data command out it does insert it but inserts the date on the tab for customer selected on both tabs. "Office_Novice" wrote: What is the Trouble? "Leanne" wrote: No dont be sorry for being cleaver - I think I should change my screen name to include novice. I have entered this - would you be able to see if I have missed something obvious - I have this entered into the code for a multipage box so have repeated it for the other entry 'Invoice' tab. Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub Thanks again "Office_Novice" wrote: Put this between end with and end sub TextBox1.Value = "" I noticed after the post Sry. Glad i could help ;o) "Leanne" wrote: Hi, Yes that works thankyou very much. Now would you belive that I can not clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub "Leanne" wrote: Hi, I have been to http://www.contextures.on.ca/xlUserForm01.html#SetUp and gained a lot of usefull information however the question I have does not seem to be addressed there. I want to create a form where the user selects an option from a combo box and then enters the information relevant into the text box. This I have done but what I do not know is how to get it to update the sheet in the correct |
Help with Forms
Dont give, Its somthing simple you are over looking i'll bet.
More then likley your code is in the wrong place. I have reviewed your other posts and you are taking on an awful lot for a beginner. It looks as though you have only been at this a few days. If you'd like i will send you the form i was using based on your post w/ how to import it to you work sheet then you can modify away. If your interested reply to this post. "Leanne" wrote: Thanks for your time - I can't belive I am saying this but I think it has defeated me. I have put this code in and changed it as necessary but still no joy. It clears the boxes fine but does not enter the data anywhere. At this stage I can not even write the code for a simple form anymore so I think I need to leave it be. I will just add a link to the sheet and the user will have to filter the list and enter the date manually. "Office_Novice" wrote: Here is what i have good luck. I hope this helps. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundCell As Range With Worksheets(1).Range("A1:A65536") Set foundCell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundCell Is Nothing Then foundCell.Offset(0, 2).Value = TextBox1.Value End If End With TextBox1.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Private Sub CommandButton2_Click() Dim foundCell As Range With Worksheets(1).Range("a1:a65536") Set foundCell = .Find(What:=ComboBox2.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundCell Is Nothing Then foundCell.Offset(0, 1).Value = TextBox2.Value End If End With TextBox1.Value = "" End Sub Private Sub CommandButton3_Click() Unload Me End Sub Private Sub CommandButton4_Click() Unload Me End Sub 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" ComboBox2.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Office_Novice" wrote: Also Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" Invoicelist.RowSource = "Sheet1!A2:A30" End Sub "Leanne" wrote: This is exatly how my code appears. The form has two tabs - one called Visit dates and the other Invoice dates. On each tab there is a combo box called VisitList (InvoiceList) and a text box called VisitDate (InvoiceDate) also a Save Entry button called SaveVisit (SaveInvoice) and an Exit form button called CloseVisit (CloseInvoice) All the information comes and goes to one sheet called Dates and it looks like this ColA ColB ColC Row1 Customer Name Invoice Date Visit Date Row2 Marchwood ERF Row3 Portsmouth ERF I hope I have provided enough information for you and I really appreciate the time you are taking to help me with this. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Sub SaveInvoice_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=InvoiceList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 21).Value = InvoiceDate.Value End If End With InvoiceList.Value = "" InvoiceDate.Value = "" End Sub 'This way your company list stays in its 'current range & only the dates change Private Sub UserForm_Initialize() VisitList.RowSource = "Dates!A2:A300" InvoiceList.RowSource = "Dates!A2:A300" End Sub 'Your ComboBox will then be able to grow as your list grows 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub CloseInvoice_Click() Unload Me End Sub Private Sub CloseVisit_Click() Unload Me End Sub "Office_Novice" wrote: You said you were using a mutipage control. I assume we are talking about the 'Invoice' Tab where are you trying to send this data? please provide Sheet names and cell addresses so may be we can cut down on editing. "Leanne" wrote: Opps - are you not a mind reader haha When I click the Save Entry button on the form it clears the data but does not insert it into the cells. With the clear data command out it does insert it but inserts the date on the tab for customer selected on both tabs. "Office_Novice" wrote: What is the Trouble? "Leanne" wrote: No dont be sorry for being cleaver - I think I should change my screen name to include novice. I have entered this - would you be able to see if I have missed something obvious - I have this entered into the code for a multipage box so have repeated it for the other entry 'Invoice' tab. Sub SaveVisit_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=VisitList.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = VisitDate.Value End If End With VisitList.Value = "" VisitDate.Value = "" End Sub Thanks again "Office_Novice" wrote: Put this between end with and end sub TextBox1.Value = "" I noticed after the post Sry. Glad i could help ;o) "Leanne" wrote: Hi, Yes that works thankyou very much. Now would you belive that I can not clear the data - I have used the same code but this time it is clearling it without inserting it. I only moved onto clearing it as I noticed that as I am working with two tabs it was updating the dates in both for the customer on the one I was saving (sorry don't know if that makes any sense) Oh and by the way - I think you should change your screen name - you are no novice! "Office_Novice" wrote: Try somthing like this You may have to modify it some for it to work for you but i think it could be a good option. Option Explicit 'This searches for the seletion in the comboBox 'Then goes to the Column "C" to input the date entered Sub CommandButton1_Click() Dim foundcell As Range With Worksheets(1).Range("A1:A65536") Set foundcell = .Find(What:=ComboBox1.Value, lookAt:=xlWhole, _ LookIn:=xlValues, SearchOrder:=xlRows, _ MatchCase:=True, MatchByte:=True) If Not foundcell Is Nothing Then foundcell.Offset(0, 2).Value = TextBox1.Value End If End With End Sub 'This way your company list stays in its 'current range & only the dates change 'This will use your Sheets 'Data to populate the ComboBox 'Change ranges to fit your data Private Sub UserForm_Initialize() ComboBox1.RowSource = "Sheet1!A2:A30" End Sub 'Your ComboBox will then be able to grow as your list grows "Leanne" wrote: Thanks for this information both of you - I cant test either of them because I am having trouble with something so simple - Cant get my combo box to display the names. Could it have something to do with the fact that the cells in the range contain a formula? Private Sub VisitList_Change() Dim CustomerName As Range Dim ws As Worksheet Set ws = Worksheet("Lookuplists") For Each VisitList In ws.Range("CustomerName") With Me.VisitList .AddItem CustomerName.Value .List(.ListCount - 1, 1) = CustomerName.Offset(0, 1).Value End With Next VisitList End Sub |
Help with Forms
|
| All times are GMT +1. The time now is 01:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com