Using VBA Set for workbook and worksheet
I have a workbook with two worksheets: Sheet1 = named Find and Sheet2 =named MasterData. On Sheet2 I have a created a table named Table2
I am have created a Userform to interact with the MasterData table2 but continue to get errors when I try to create SET statement in the module.
Here are the Dim statements in procedure:
Code:
Dim wb As Workbook
Dim ws1 As Worksheet
Dim tbl1 As ListObject
The module I am having trouble with should process like this (after clicking a record in the forms listbox it should select that records info from sheet2 data and fill individual text boxes)
Here is the code section (with lines identified as showing errors)
Code:
Private Sub lbo1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Me.lbo1.ListIndex = -1 Then Exit Sub
If Me.lbo1.ListIndex > -1 Then sc = Me.lbo1.ListIndex + 2
ClearForm
Set ws1 = wb.Sheets("Sheet2") 'errors out
Set tbl1 = ws1.ListObjects("Table2") 'errors out
With tbl1
For i = 1 To 6
Controls("txtbox" & i).Value = .Range(sc, i)
Next i
End With
End Sub
How should these SET statements be modified? Thank you.
All the code for the problem
Thank you for your reply.
I do have a named range named 'Table2'.
With your questions I thought I should provide you the code I am working with.
Code:
Option Explicit
Dim wb As Workbook
Dim ctl As Control
Dim ary1 As Variant
Dim ws As Worksheet
Dim tbl1 As ListObject
Sub ClearForm()
For Each ctl In uf1.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "ListBox"
ctl.ListIndex = -1
End Select
Next ctl
End Sub
Sub LoadListBox()
Set wb = ThisWorkbook
Set ws = wb.Sheets("MasterData")
Set tbl1 = ws.ListObjects("Table2") 'this is the offending line - Set ws line above does not error out
With tbl1
If .Range(2, 1) = "" Then Exit Sub
ary1 = .DataBodyRange
End With
With uf1.lbo1
.List = ary1
.ColumnCount = 6
.ColumnWidths = "50,50,30,20,20,20"
End With
End Sub
Again, your help is really appreciated.
1 Attachment(s)
Here is the Workbook being referenced in the thread
Here is a copy with data data for your review.
The Find page works as designed. The error occurs when you click the button at the bottom of the page.
I just noticed that the version I sent you did not have the command button at the bottom of the Find page.
The code is included in VB editor. The name of the button is AddEditMod
If you edit the buttons code (below) into the Sheet1(FIND) module it will all work correctly.
Code:
Private Sub AddEditMod_Click()
uf1.Show
End Sub
The list should be displaying
Quote:
Originally Posted by
June7
Why is there still no button on the worksheet to open form? Looks exactly like the first file you posted.
Okay, I can get the form to run by commenting out call to LoadListBox procedure in form's code (once I figured out how to view form code), as this is cause of error.
Where is this "Table2" referenced in listbox load procedure? It is not listed in Name Manager. What data should load into listbox?
I created a Table2 and now code runs without error.
Since the Table 2 now works, the ufi form the lbo1 (lable: Search Site Mane) control should be populated with the data in Table2
1 Attachment(s)
Image of the Find Page with Command Button
Just in case the newest file I sent does not show the command button on the Find page, I am attaching an image of that page.
1 Attachment(s)
Sorry to bother you again but I ran into problem with your range fix
When I try to delete the TableX name in name manager the delete option is greyed out. Do you know how I can fix that?
I have tried numerous programs and vba code to delete the TableX named range without success. Each and every one of the solutions tried doesn't recognize the name range and/or doesn't show it in the list of available names.
On of the vba codes I tried is:
Code:
Dim wb As Workbook
Dim nr As Name
Set wb = ActiveWorkbook
Set nr = wb.Names.Item("TableX")
' give an absolute reference:
nr.RefersTo = "=MasterData!$A$3:$G$400" error reads Run-time error'1004' Application defined or object-defined error
End With
I am not sure where to go now. Maybe you have an idea?
