Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
why is this form code not working
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 "JLGWhiz" wrote: 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. "Leanne" wrote: 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. "JLGWhiz" wrote: 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. "Leanne" wrote: 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
why is this form code not working
I am having a hard time visualizing your set up, but here is what it looks
like based on the code you posted. You are trying to use code in a standard module to utilize values contained in a control on a form. If this is true, and if the form is closed (unloaded) when you try to use the control (listbox) value, it will not work because the control no longer has a value. The form must be open at the time you refer to the listbox value, or you must have stored the value to a place that can be accessed after the form closes. I would offer some sample code, but I don't want to confuse the issue even more at this point. I'll let you mull this over and see if you can figure it out on your own. If not, make a new posting with your code and start again. Maybe somebody smarter than me can tell you how to work with what you have. "Leanne" wrote: 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 "JLGWhiz" wrote: 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. "Leanne" wrote: 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. "JLGWhiz" wrote: 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. "Leanne" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
why is this form code not working
Hi, I am having a hard time explaining what it is I am trying to do so it is
no wonder you are having trouble. I think it is something that should be fairly easy but I just do not know enough about code or forms. I spent a while on a post with Office_Novice who turned out to be less of a novice than me but we still could not crack it so I doubt I will be able to do it on my own. Is there anyway you would be willing to let me send you the details so that you could look at them better? "JLGWhiz" wrote: I am having a hard time visualizing your set up, but here is what it looks like based on the code you posted. You are trying to use code in a standard module to utilize values contained in a control on a form. If this is true, and if the form is closed (unloaded) when you try to use the control (listbox) value, it will not work because the control no longer has a value. The form must be open at the time you refer to the listbox value, or you must have stored the value to a place that can be accessed after the form closes. I would offer some sample code, but I don't want to confuse the issue even more at this point. I'll let you mull this over and see if you can figure it out on your own. If not, make a new posting with your code and start again. Maybe somebody smarter than me can tell you how to work with what you have. "Leanne" wrote: 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 "JLGWhiz" wrote: 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. "Leanne" wrote: 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. "JLGWhiz" wrote: 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. "Leanne" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set Focus Not working on a form | Excel Programming | |||
Tab order on User Form not working | Excel Programming | |||
Form Buttons Not Working | Excel Programming | |||
First text box in form not working right | Excel Programming | |||
Form control not working | Excel Programming |