Variable Not Defined

[Mooseman60]

Hi

I have spent 2 nights trying to solve why i keep getting the error Variable Not Defined

My userform has 1 combo box to display Job Name and 1 text box to display job number from list in worksheet named JobName in separate workbook called Defined Name Lists.xls

The error appears on this line of code


Me.txtJobNo.Value = JobNos(theIndex)



Option Explicit
Const cListFile As String = "Defined Name Lists.xls" ' the file containing the combobox data
Const cListSheet As String = "JobName" ' the worksheet containing the list
Const cJobNameColumn As String = "A" ' the column containing the job name
Const cJobNoColumn As String = "B" ' the column containing the job number
Const cHasHeader As Boolean = True ' does the list have a header in row 1
Dim JobNames() As String ' array to hold the job name
Dim JobNos() As Single ' array to hold the job no

Private Sub cboJobName_Click()
' get the index of the item selected in the combobox
Dim theIndex As Long
theIndex = Me.cboJobName.ListIndex + 1

' update the job number

Me.txtJobNo.Value = JobNos(theIndex)

' Dim rw As Long
' With Worksheets("JobName")
' rw = .Range("$A2:$A50").Find(Me.cboJobName.Text, LookIn:=xlValues, _
' lookat:=xlWhole, MatchCase:=False).Row
' Me.txtJobName.Text = .Range("A" & rw).Value
' Me.txtJobNo.Text = .Range("B" & rw).Value
' End With
End Sub
Private Sub UserForm_Initialize()
' open the workbook containing the data to load in combox1 read only
' and hide it
Dim theWB As Workbook
Set theWB = Workbooks.Open(Filename:="Defined Name Lists.xls", ReadOnly:=True)
theWB.Windows(1).Visible = False
' address the combox list
Dim theSheet As Worksheet
Set theSheet = theWB.Worksheets(cListSheet)
' array size counter
Dim nList As Long
nList = 0

' loop through all of the list loading the values into the combobox
Dim rw As Range
For Each rw In theSheet.Rows
' skip row 1 if there is a header
If (rw.Row = 1 And cHasHeader) Then
' stop on the first blank cell
ElseIf (rw.Cells(1, 1).Value = "") Then
Exit For
Else
' move the data from the list workbook to the combox
cboJobName.AddItem rw.Cells(1, cJobNameColumn).Value

' update the job name and job number text boxes
Me.txtJobNo.Value = JobNos(theIndex)

' retain the job name and job no
nList = nList + 1
ReDim Preserve JobNames(1 To nList)
ReDim Preserve JobNos(1 To nList)
JobNames(nList) = rw.Cells(1, cJobNameColumn).Value
JobNos(nList) = rw.Cells(1, cJobNoColumn).Value
End If
Next rw
' close the workbook and release storage
theWB.Close SaveChanges:=False
Set theWB = Nothing
Set theSheet = Nothing
End Sub
:banghead:

[Bob Phillips]

Can you post the workbook?

[Mooseman60]

Thanks

I have attached the workbook

[Bob Phillips]

How do I force the error?

[Mooseman60]

Open the workbook and click on Project Explorer open frmWelcome Screen click on Run Subform and error should come up

[Bob Phillips]

I see it now, you define theIndex in the combobox procedure but not in the userform procedure, so you get the error.

[Mooseman60]

I see it now, you define theIndex in the combobox procedure but not in the userform procedure, so you get the error.

How and where do i define theIndex in the userform procedure

[Bob Phillips]

Just Dim it as you did in the other procedure, and make sure that you load the variable.

[Mooseman60]

Just Dim it as you did in the other procedure, and make sure that you load the variable.

I've got very basic knowledge of VBA and don't understand where to insert the code in the userform can you write the code for me and show me where to insert it in userform

Thanks

[Bob Phillips]

Looking at it again, you could probably get away with removing these lines from the Userform_Initialize procedure



' update the job name and job number text boxes
Me.txtJobNo.Value = JobNos(theIndex)

[Mooseman60]

Looking at it again, you could probably get away with removing these lines from the Userform_Initialize procedure



' update the job name and job number text boxes
Me.txtJobNo.Value = JobNos(theIndex)


The userform initialize procedure doesn't have this line of code. I deleted it from the combo box procedure but it still comes up with the error Type Mismatch

Any other ideas

Thanks

[Bob Phillips]

Yes it does. It even appears in the code you posted above.

[Ryan Remole]

I modified your workbook to an extent. It may give you some ideas.
Also, read this recent thread (http://www.vbaexpress.com/forum/showthread.php?t=34760) for maybe some additional ideas.

[Mooseman60]

I modified your workbook to an extent. It may give you some ideas.
Also, read this recent thread (http://www.vbaexpress.com/forum/showthread.php?t=34760) for maybe some additional ideas.

Ryan

When I open the frmWelcomeSheet it opens in design view when i select run subform/userform it comes up with File Not Found The file and/or directory have changed Do you want to reset the destination now. If i say no it opens in form view but there are no jobs to select from the combo box

I'll give you a run down on what i am trying to achieve.

When the workbook Project Costs Temp.xls is selected it should open the Welcome Sreen Form. From the combo box i should be able to select a job name which will then populate the text box with the job number. I then click on the command button which will search the file named Defined Name Lists.xls in Documents and open the worksheet Job Name where the Job Name and Job Number are stored this intern open that workbook.
I can then add additional data and then close and save those changes in the workbook.xls that was selected
If no job name can be found in the combo box click then click on add Job Name/Job Number command button and create new workbook saved as the Job Number. This job number is saved as an .xls file to a folder Called Job Numbers in Documents. You then go back to the Welome Screen Form and select the new Job Name from the combo box. Which will search the folder Job Number and open the corresponding wor: pray2: kbook Job Number.xls

I have put all the employers, suppliers etc details that are selected by using combo boxes on the various userforms on the frmMenu in a seperate workbook called Defined Name Lists.xls so that if additional employer, supplier imformation is added it is saved to the Defined Name List.xls file so that it will be available for other workbooks to be able to select from as well
The Project Costs Temp.xls file is basically used as a shell template and should remain blank from data each time it is opened all data entered into it should be saved to each individual Job No.xls file.

I hope this makes sense

[Ryan Remole]

When I open the frmWelcomeSheet it opens in design view when i select run subform/userform it comes up with File Not Found The file and/or directory have changed Do you want to reset the destination now. If i say no it opens in form view but there are no jobs to select from the combo box

It worked with 'Activate', but not with 'Open'. 1- I forgot to add Range("A1"). 2-Your selecting 'no' killed the option to set the directory and to know where to find the data- (which in my test, I put in Column-A of "JobName" worksheet) I also changed the description in my "File Not Found" error. Your welcome to use the old phrasing when your employees start moving your Job workbooks to various places in different drives!





When the workbook Project Costs Temp.xls is selected it should open the Welcome Sreen Form. From the combo box i should be able to select a job name which will then populate the text box with the job number. I then click on the command button which will search the file named Defined Name Lists.xls in Documents and open the worksheet Job Name where the Job Name and Job Number are stored this intern open that workbook.

I think it would be easier to pull the job#'s and the job names into a listbox. Then use this selection to open up the corresponding JobNo workbook. I want to get your opinion on that before I spend the time suggesting additional code.

I will try to assist when I can. This thread may get moved to 'Project Assistance' and out of 'Excel Help'.




I have put all the employers, suppliers etc details that are selected by using combo boxes on the various userforms on the frmMenu in a seperate workbook called Defined Name Lists.xls so that if additional employer, supplier imformation is added it is saved to the Defined Name List.xls file so that it will be available for other workbooks to be able to select from as well
The Project Costs Temp.xls file is basically used as a shell template...


Good idea!

Sheet1 and Sheeet12 contained code for controls. Were these just placed there ''out of the way' or are there supposed to be ActiveX controls on these sheets?





Private Sub UserForm_Initialize()
On Error GoTo Errorhandler
Application.ScreenUpdating = False
Dim h As Workbook, var1 As Long
For Each h In Workbooks
If h.Name = Sheet2.Range("B2").Value Then
var1 = 1 'is open
Exit For
End If
Next
If var1 = 1 Then
Windows(Sheet2.Range("B2").Value).Activate
Else
Workbooks.Open (Sheet2.Range("A2").Value & "\" & Sheet2.Range("B2").Value)
End If
'If ActiveWorkbook.ReadOnly = True Then ActiveWorkbook.ChangeFileAccess xlReadOnly
'not sure why you would need it in ReadOnly since you are going to close it back down anyway.
'If another employee has it open, it would be read only anyway.
'Also, (you may need this later) if an employee opens a file and vba changes it to read-only,
'everyone else is still stuck with read-only access until the file is closed.
Dim var2, var3
For var2 = 0 To Sheets("JobName").Range("A1").CurrentRegion.Rows.Count
var3 = Sheets("JobName").Range("A2").Offset(var2, 0).Value
If var3 <> "" Then cboJobName.AddItem var3
Next
Application.ScreenUpdating = True
'if "Defined Name Lists" was already open then keep it open, else close.
If var1 = 0 Then ActiveWorkbook.Close SaveChanges:=False

Exit Sub
Errorhandler:
If Err = 1004 Then
var2 = MsgBox("Unable to locate ""Defined Name Lists"". Do you" & VBA.Chr(13) & _
"want to locate this workbook now?", vbYesNo + vbCritical, "FILE NOT FOUND")
If var2 = vbYes Then
Dim NewBookName, NewBookPath
Application.ScreenUpdating = False
Application.FindFile
If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub
NewBookName = ActiveWorkbook.Name
NewBookPath = ActiveWorkbook.Path
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
ThisWorkbook.Activate
Sheet2.Range("A2").Value = NewBookPath
Sheet2.Range("B2").Value = NewBookName
MsgBox "The new location has been set."
Application.ScreenUpdating = True
End If
End If
End Sub

[Mooseman60]

It worked with 'Activate', but not with 'Open'. 1- I forgot to add Range("A1"). 2-Your selecting 'no' killed the option to set the directory and to know where to find the data- (which in my test, I put in Column-A of "JobName" worksheet) I also changed the description in my "File Not Found" error. Your welcome to use the old phrasing when your employees start moving your Job workbooks to various places in different drives!




I think it would be easier to pull the job#'s and the job names into a listbox. Then use this selection to open up the corresponding JobNo workbook. I want to get your opinion on that before I spend the time suggesting additional code.

I will try to assist when I can. This thread may get moved to 'Project Assistance' and out of 'Excel Help'.




Good idea!

Sheet1 and Sheeet12 contained code for controls. Were these just placed there ''out of the way' or are there supposed to be ActiveX controls on these sheets?





Private Sub UserForm_Initialize()
On Error GoTo Errorhandler
Application.ScreenUpdating = False
Dim h As Workbook, var1 As Long
For Each h In Workbooks
If h.Name = Sheet2.Range("B2").Value Then
var1 = 1 'is open
Exit For
End If
Next
If var1 = 1 Then
Windows(Sheet2.Range("B2").Value).Activate
Else
Workbooks.Open (Sheet2.Range("A2").Value & "\" & Sheet2.Range("B2").Value)
End If
'If ActiveWorkbook.ReadOnly = True Then ActiveWorkbook.ChangeFileAccess xlReadOnly
'not sure why you would need it in ReadOnly since you are going to close it back down anyway.
'If another employee has it open, it would be read only anyway.
'Also, (you may need this later) if an employee opens a file and vba changes it to read-only,
'everyone else is still stuck with read-only access until the file is closed.
Dim var2, var3
For var2 = 0 To Sheets("JobName").Range("A1").CurrentRegion.Rows.Count
var3 = Sheets("JobName").Range("A2").Offset(var2, 0).Value
If var3 <> "" Then cboJobName.AddItem var3
Next
Application.ScreenUpdating = True
'if "Defined Name Lists" was already open then keep it open, else close.
If var1 = 0 Then ActiveWorkbook.Close SaveChanges:=False

Exit Sub
Errorhandler:
If Err = 1004 Then
var2 = MsgBox("Unable to locate ""Defined Name Lists"". Do you" & VBA.Chr(13) & _
"want to locate this workbook now?", vbYesNo + vbCritical, "FILE NOT FOUND")
If var2 = vbYes Then
Dim NewBookName, NewBookPath
Application.ScreenUpdating = False
Application.FindFile
If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub
NewBookName = ActiveWorkbook.Name
NewBookPath = ActiveWorkbook.Path
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
ThisWorkbook.Activate
Sheet2.Range("A2").Value = NewBookPath
Sheet2.Range("B2").Value = NewBookName
MsgBox "The new location has been set."
Application.ScreenUpdating = True
End If
End If
End Sub



Ryan

Please go ahead with the code for the list box if you think this is the way to go:thumb