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

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

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

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

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

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

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

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