What is missing from this form code?

L

Leanne

Can anyone help identify why this is not working correctly. Following is the
full code from the form. Myself and another member have worked on this but
can not resolve the problem.

This code is used for a 2 page form that takes information from the sheet to
create the combo Boxes (.....List).

What it is not doing, is entering the data from the text box (.....Date)
back into the sheet (Called Dates - Invoice goes in col B and Visit Col C) -
against the entry that has been selected from the combo box (range of
customer names from col A).

----------------------------------
Option Explicit

'This searches for the seletion in the comboBox
'Then goes to the Column 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
-------------------------------------------------
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
--------------------------------------------------
Private Sub CloseVisit_Click()
Unload Me
End Sub
---------------------------------------------------
Private Sub CloseInvoice_Click()
Unload Me
End Sub
---------------------------------------------------
'This will use your Sheets
'Data to populate the ComboBox
'Your ComboBox will then be able to grow as your list grows

Private Sub UserForm_Initialize()
VisitList.RowSource = "Dates!A2:A300"
InvoiceList.RowSource = "Dates!A2:A300"
End Sub
 
J

JLGWhiz

where do you declare your VisitDate variable or is that the name of your
listbox?
If that is the name of the listbox then what is VisitList? I think your
problem lies between these two. One is apparently a name and the other a
variable. The variable has not been declared.
 
L

Leanne

VisitDate is the name of my text box and VisitList is the name of the combo
box.
Not knowing much about VBA code I thought the last entry did this - as the
list of customers will grow with time so I need the combo box to grow with
the list.

If this is not the case what do I need to enter and where.
 
J

JLGWhiz

Then it appears that the listbox value is not what you think it is. Have you
stepped through the procedure, using F8 key, to check the values? You can
mouse over the variables and names of objects to see their values after each
step of the code executes. Or you can open the immediate window in the VBE
and watch as you step though to see what the values are. I suspect your list
box value will show null.
I'm guessing that the list box is not on a form, but on a sheet, so you
would probably have to set focus on the listbox to read the value.
 
L

Leanne

Thanks, I will give this a go and get back to you in a few hours if that is ok.
Thanks
 
L

Leanne

Yes you are correct, when I hover over visitlist and invoicelist it shows ""
- could that have something to do with the fact that I am trying to clear the
data when saving. The list box (combobox) is on the form but it does pull
its list from a sheet. If I have to set focus or define a range can you tell
me how to do it please. I am a real beginer at this. Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top