Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > "Variable not defined" error for form that does not initially exist...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default "Variable not defined" error for form that does not initially exist...
We are using a complex macro to handle importing into a retail item
database. When the program is run, three forms exist. A fourth form is
created through VBA using the following code while the program is
running:

Dim newButton As MSForms.CommandButton
Dim newLabel As MSForms.Label
Dim newCombo As MSForms.ComboBox
Dim rownum As Integer
Dim line As Integer
Dim formHeight As Integer
Dim scrollHeight As Integer
Dim X As Integer
Dim XMax As Integer
Dim excelName As String
Dim mapName As String
Dim excelID As String
Dim impColCount As Integer
Dim rst As ADODB.Recordset

userForm4Load = 1

If UserForm1.TextBox1.Enabled = True Then
excelName = Trim(UserForm1.TextBox1.Value)
Else
excelName = Trim(UserForm1.ComboBox1.Value)
End If

Set rst = New ADODB.Recordset

rst.ActiveConnection = conn

rst.Open "SELECT excel_id FROM excel WHERE name = '" & excelName &
"'"

excelID = rst("excel_id")

Set rst = New ADODB.Recordset

rst.ActiveConnection = conn

rst.Open "SELECT COUNT(excel_detail_id) AS count FROM excel_detail
WHERE excel_id = " & excelID

impColCount = rst("count")

If impColCount 0 Then
XMax = impColCount - 1
Else
XMax = colCount - 1
End If

formHeight = 98
For X = 0 To XMax
If formHeight < 367 Then
formHeight = formHeight + 24
Else
Exit For
End If
Next

scrollHeight = 98
For X = 0 To XMax
scrollHeight = scrollHeight + 24
Next

'This is to stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False

Set tempForm = ThisWorkbook.VBProject.VBComponents.add(3)

'Create the User Form
With tempForm
.Properties("Caption") = excelName & ": Map Columns"
.Properties("Width") = 461.5
.Properties("Height") = formHeight
.Properties("BackColor") = &HFFC0C0
.Properties("ScrollBars") = fmScrollBarsVertical
.Properties("ScrollHeight") = scrollHeight
.Properties("Visible") = False
End With

Set newLabel = tempForm.designer.Controls.add("Forms.label.1")
With newLabel
.Name = "MainLabel"
.Caption = "Map the columns below to the appropriate fields.
Required fields are indicated by an asterisk (*) after the column
description. Each column may only be assigned to one field."
.Top = 0
.Left = 12
.Width = 420
.Height = 24
.BackColor = &HC0C0C0
.BorderStyle = fmBorderStyleSingle
.TextAlign = fmTextAlignCenter
End With

Set newLabel = tempForm.designer.Controls.add("Forms.label.1")
With newLabel
.Name = "Col1Label"
.Caption = "Column Names"
.Font.Bold = True
.Top = 24
.Left = 18
.Width = 120
.Height = 12
.TextAlign = fmTextAlignCenter
End With

Set newLabel = tempForm.designer.Controls.add("Forms.label.1")
With newLabel
.Name = "Col2Label"
.Caption = "Sample Data"
.Font.Bold = True
.Top = 24
.Left = 162
.Width = 120
.Height = 12
.TextAlign = fmTextAlignCenter
End With

Set newLabel = tempForm.designer.Controls.add("Forms.label.1")
With newLabel
.Name = "Col3Label"
.Caption = "RMS Data Field"
.Font.Bold = True
.Top = 24
.Left = 306
.Width = 120
.Height = 12
.TextAlign = fmTextAlignCenter
End With

Set newButton =
tempForm.designer.Controls.add("Forms.commandbutto n.1")
With newButton
.Name = "CommandButton1"
.Caption = "< Back"
.Top = 48 + (24 * X)
.Left = 18
.Width = 78
.Height = 24
End With

Set newButton =
tempForm.designer.Controls.add("Forms.commandbutto n.1")
With newButton
.Name = "CommandButton2"
.Caption = "Cancel..."
.Top = 48 + (24 * X)
.Left = 183
.Width = 78
.Height = 24
End With

Set newButton =
tempForm.designer.Controls.add("Forms.commandbutto n.1")
With newButton
.Name = "CommandButton3"
.Caption = "Finish Import"
.Top = 48 + (24 * X)
.Left = 348
.Width = 78
.Height = 24
End With

'Create Column 1 Labels
If UserForm3.OptionButton2.Value = False Then
For X = 0 To XMax
Set newLabel = tempForm.designer.Controls.add("Forms.label.
1")
With newLabel
.Name = "Label" & X + 1
.Caption = Left(Trim(cells(9, X + 1).Value), 30)
.Top = 24 + (24 * (X + 1))
.Left = 18
.Width = 120
.Height = 12
End With
Next
Else
For X = 0 To XMax
Set newLabel = tempForm.designer.Controls.add("Forms.label.
1")
With newLabel
.Name = "Label" & X + 1
.Caption = "Column " & X + 1
.Top = 24 + (24 * (X + 1))
.Left = 18
.Width = 120
.Height = 12
End With
Next
End If

'Create Column 2 Labels
For X = 0 To XMax
If UserForm3.OptionButton2.Value = False Then
rownum = 10
Else
rownum = 9
End If
Set newLabel = tempForm.designer.Controls.add("Forms.label.1")
Do Until cells(rownum, X + 1).Value < Empty Or rownum = 2000
rownum = rownum + 1
Loop
With newLabel
.Name = "Label" & X + 1001
.Caption = Left(Trim(cells(rownum, X + 1).Value), 30)
.Top = 24 + (24 * (X + 1))
.Left = 162
.Width = 120
.Height = 12
End With
Next
Dim test As String
'Create Column 3 Combo Boxes
If impColCount 0 Then
For X = 0 To XMax
Set rst = New ADODB.Recordset

rst.ActiveConnection = conn

rst.Open "SELECT i.imp_col_label FROM imp_col i,
excel_detail e WHERE e.imp_col_id = i.imp_col_id AND e.excel_id = " &
excelID & " AND e.xls_col_index = " & X + 1

mapName = rst("imp_col_label")

Set newCombo =
tempForm.designer.Controls.add("Forms.combobox.1")
With newCombo
.Name = "ComboBox" & X + 1
.Value = Trim(mapName)
.TabIndex = X
.Top = 18 + (24 * (X + 1))
.Left = 306
.Width = 120
.Height = 18
.RowSource = "Data!B1:B49"
End With
Next
Else
For X = 0 To XMax
Set newCombo =
tempForm.designer.Controls.add("Forms.combobox.1")
With newCombo
.Name = "ComboBox" & X + 1
.Value = "(Ignore)"
.Top = 18 + (24 * (X + 1))
.Left = 306
.TabIndex = X
.Width = 120
.Height = 18
.RowSource = "Data!B1:B48"
End With
Next
End If

With tempForm.codemodule
line = .countoflines
.insertlines line + 1, "Sub CommandButton1_Click"
.insertlines line + 2, "UserForm4.Hide"
.insertlines line + 3, "UserForm3.Show"
.insertlines line + 4, "End Sub"
.insertlines line + 5, "Sub CommandButton2_Click"
.insertlines line + 6, "loadLine = 0"
.insertlines line + 7,
"ThisWorkbook.VBProject.VBComponents.Remove tempForm"
.insertlines line + 8, "Unload UserForm1"
.insertlines line + 9, "Unload UserForm2"
.insertlines line + 10, "Unload UserForm3"
.insertlines line + 11, "Unload UserForm4"
.insertlines line + 12, "End Sub"
.insertlines line + 13, "Sub CommandButton3_Click"
.insertlines line + 14, "handleUserForm4"
.insertlines line + 15, "End Sub"
End With

'Show the form using differenet sub
VBA.UserForms.add (tempForm.Name)

showUserForm4

I can then show and hide the form using "UserForm4.Show" and
"UserForm4.Hide". The form is removed when the program is finished
running (it is built on user inputs so it needs to be recreated every
time).

My trouble is that I need to password protect this vba script to
prevent unauthorized edits / access. I can't because the variable
"UserForm4" is not defined and the compiler does not recognize it as a
valid object because it does not exist until the middle of the script.
I have tried defining the variable as an object, but as soon as I do
that, the form will create but not show.

Any ideas?

-- Alex Nielsen

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default "Variable not defined" error for form that does not initially exis
Insert a blank userform4 into the code using the Insert form in the VB
window. Change the name of the new form to userform4.

Delete the blank userform4 in your code and replace with new form. either
that or just modify the userform4 with VBA code.

"Alex" wrote:

We are using a complex macro to handle importing into a retail item
database. When the program is run, three forms exist. A fourth form is
created through VBA using the following code while the program is
running:

Dim newButton As MSForms.CommandButton
Dim newLabel As MSForms.Label
Dim newCombo As MSForms.ComboBox
Dim rownum As Integer
Dim line As Integer
Dim formHeight As Integer
Dim scrollHeight As Integer
Dim X As Integer
Dim XMax As Integer
Dim excelName As String
Dim mapName As String
Dim excelID As String
Dim impColCount As Integer
Dim rst As ADODB.Recordset

userForm4Load = 1

If UserForm1.TextBox1.Enabled = True Then
excelName = Trim(UserForm1.TextBox1.Value)
Else
excelName = Trim(UserForm1.ComboBox1.Value)
End If

Set rst = New ADODB.Recordset

rst.ActiveConnection = conn

rst.Open "SELECT excel_id FROM excel WHERE name = '" & excelName &
"'"

excelID = rst("excel_id")

Set rst = New ADODB.Recordset

rst.ActiveConnection = conn

rst.Open "SELECT COUNT(excel_detail_id) AS count FROM excel_detail
WHERE excel_id = " & excelID

impColCount = rst("count")

If impColCount 0 Then
XMax = impColCount - 1
Else
XMax = colCount - 1
End If

formHeight = 98
For X = 0 To XMax
If formHeight < 367 Then
formHeight = formHeight + 24
Else
Exit For
End If
Next

scrollHeight = 98
For X = 0 To XMax
scrollHeight = scrollHeight + 24
Next

'This is to stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False

Set tempForm = ThisWorkbook.VBProject.VBComponents.add(3)

'Create the User Form
With tempForm
.Properties("Caption") = excelName & ": Map Columns"
.Properties("Width") = 461.5
.Properties("Height") = formHeight
.Properties("BackColor") = &HFFC0C0
.Properties("ScrollBars") = fmScrollBarsVertical
.Properties("ScrollHeight") = scrollHeight
.Properties("Visible") = False
End With

Set newLabel = tempForm.designer.Controls.add("Forms.label.1")
With newLabel
.Name = "MainLabel"
.Caption = "Map the columns below to the appropriate fields.
Required fields are indicated by an asterisk (*) after the column
description. Each column may only be assigned to one field."
.Top = 0
.Left = 12
.Width = 420
.Height = 24
.BackColor = &HC0C0C0
.BorderStyle = fmBorderStyleSingle
.TextAlign = fmTextAlignCenter
End With

Set newLabel = tempForm.designer.Controls.add("Forms.label.1")
With newLabel
.Name = "Col1Label"
.Caption = "Column Names"
.Font.Bold = True
.Top = 24
.Left = 18
.Width = 120
.Height = 12
.TextAlign = fmTextAlignCenter
End With

Set newLabel = tempForm.designer.Controls.add("Forms.label.1")
With newLabel
.Name = "Col2Label"
.Caption = "Sample Data"
.Font.Bold = True
.Top = 24
.Left = 162
.Width = 120
.Height = 12
.TextAlign = fmTextAlignCenter
End With

Set newLabel = tempForm.designer.Controls.add("Forms.label.1")
With newLabel
.Name = "Col3Label"
.Caption = "RMS Data Field"
.Font.Bold = True
.Top = 24
.Left = 306
.Width = 120
.Height = 12
.TextAlign = fmTextAlignCenter
End With

Set newButton =
tempForm.designer.Controls.add("Forms.commandbutto n.1")
With newButton
.Name = "CommandButton1"
.Caption = "< Back"
.Top = 48 + (24 * X)
.Left = 18
.Width = 78
.Height = 24
End With

Set newButton =
tempForm.designer.Controls.add("Forms.commandbutto n.1")
With newButton
.Name = "CommandButton2"
.Caption = "Cancel..."
.Top = 48 + (24 * X)
.Left = 183
.Width = 78
.Height = 24
End With

Set newButton =
tempForm.designer.Controls.add("Forms.commandbutto n.1")
With newButton
.Name = "CommandButton3"
.Caption = "Finish Import"
.Top = 48 + (24 * X)
.Left = 348
.Width = 78
.Height = 24
End With

'Create Column 1 Labels
If UserForm3.OptionButton2.Value = False Then
For X = 0 To XMax
Set newLabel = tempForm.designer.Controls.add("Forms.label.
1")
With newLabel
.Name = "Label" & X + 1
.Caption = Left(Trim(cells(9, X + 1).Value), 30)
.Top = 24 + (24 * (X + 1))
.Left = 18
.Width = 120
.Height = 12
End With
Next
Else
For X = 0 To XMax
Set newLabel = tempForm.designer.Controls.add("Forms.label.
1")
With newLabel
.Name = "Label" & X + 1
.Caption = "Column " & X + 1
.Top = 24 + (24 * (X + 1))
.Left = 18
.Width = 120
.Height = 12
End With
Next
End If

'Create Column 2 Labels
For X = 0 To XMax
If UserForm3.OptionButton2.Value = False Then
rownum = 10
Else
rownum = 9
End If
Set newLabel = tempForm.designer.Controls.add("Forms.label.1")
Do Until cells(rownum, X + 1).Value < Empty Or rownum = 2000
rownum = rownum + 1
Loop
With newLabel
.Name = "Label" & X + 1001
.Caption = Left(Trim(cells(rownum, X + 1).Value), 30)
.Top = 24 + (24 * (X + 1))
.Left = 162
.Width = 120
.Height = 12
End With
Next
Dim test As String
'Create Column 3 Combo Boxes
If impColCount 0 Then
For X = 0 To XMax
Set rst = New ADODB.Recordset

rst.ActiveConnection = conn

rst.Open "SELECT i.imp_col_label FROM imp_col i,
excel_detail e WHERE e.imp_col_id = i.imp_col_id AND e.excel_id = " &
excelID & " AND e.xls_col_index = " & X + 1

mapName = rst("imp_col_label")

Set newCombo =
tempForm.designer.Controls.add("Forms.combobox.1")
With newCombo
.Name = "ComboBox" & X + 1
.Value = Trim(mapName)
.TabIndex = X
.Top = 18 + (24 * (X + 1))
.Left = 306
.Width = 120
.Height = 18
.RowSource = "Data!B1:B49"
End With
Next
Else
For X = 0 To XMax
Set newCombo =
tempForm.designer.Controls.add("Forms.combobox.1")
With newCombo
.Name = "ComboBox" & X + 1
.Value = "(Ignore)"
.Top = 18 + (24 * (X + 1))
.Left = 306
.TabIndex = X
.Width = 120
.Height = 18
.RowSource = "Data!B1:B48"
End With
Next
End If

With tempForm.codemodule
line = .countoflines
.insertlines line + 1, "Sub CommandButton1_Click"
.insertlines line + 2, "UserForm4.Hide"
.insertlines line + 3, "UserForm3.Show"
.insertlines line + 4, "End Sub"
.insertlines line + 5, "Sub CommandButton2_Click"
.insertlines line + 6, "loadLine = 0"
.insertlines line + 7,
"ThisWorkbook.VBProject.VBComponents.Remove tempForm"
.insertlines line + 8, "Unload UserForm1"
.insertlines line + 9, "Unload UserForm2"
.insertlines line + 10, "Unload UserForm3"
.insertlines line + 11, "Unload UserForm4"
.insertlines line + 12, "End Sub"
.insertlines line + 13, "Sub CommandButton3_Click"
.insertlines line + 14, "handleUserForm4"
.insertlines line + 15, "End Sub"
End With

'Show the form using differenet sub
VBA.UserForms.add (tempForm.Name)

showUserForm4

I can then show and hide the form using "UserForm4.Show" and
"UserForm4.Hide". The form is removed when the program is finished
running (it is built on user inputs so it needs to be recreated every
time).

My trouble is that I need to password protect this vba script to
prevent unauthorized edits / access. I can't because the variable
"UserForm4" is not defined and the compiler does not recognize it as a
valid object because it does not exist until the middle of the script.
I have tried defining the variable as an object, but as soon as I do
that, the form will create but not show.

Any ideas?

-- Alex Nielsen

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
"User-defined type not defined" error when trying to send e-mail SupperDuck Excel Programming 9 August 2nd 06 07:36 AM
1004 "Application-defined or object-defined error" guiu Excel Programming 6 January 12th 06 02:43 PM
What does "Application-defined or object-defined error" mean? Shatin Excel Programming 4 February 3rd 04 10:15 AM
"Application-defined or object-defined error" while printing excel report chemburkar Excel Programming 0 February 2nd 04 08:33 PM
"Run Time Error 1004 Application Defined or Object Defined Error." BJC Excel Programming 4 October 26th 03 03:09 AM


All times are GMT +1. The time now is 03:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement