Using VBA Set for workbook and worksheet

[StudentJacks]

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:


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)


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.

[June7]

You haven't Set the wb object or was that done in another procedure? Where are those Dim statements located - not in the procedure you posted.

Should add Option Explicit to the top of all modules.

[StudentJacks]

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.


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.

[June7]

Now, I have to ask - why use Excel if what you need is a database?

And where is ws1 variable declared? In one procedure you use ws and another ws1.

Not interested in trying to build workbook to replicate issue.

You could provide the workbook for analysis.

[StudentJacks]

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.


Private Sub AddEditMod_Click()
uf1.Show
End Sub

[StudentJacks]

I am a database nerd and would have done it using Access but I have a close friend (and my wife) want nothing to do with DB's so am using Excel. I will be using this for me also as a password manager from a flashdrive so I can get away from online PW mgr. I may work om putting together a runtime version in db later on. Thanks, I really appreciated your help.

[June7]

Are you saying I need to create a button on Sheet1 and attach the code to it?
I did that and click button. Errors "subscript out of range" on uf1.Show.

Why does combobox list have only one item?

[StudentJacks]

If you highlight the combobox list item and type in a w, it should show the list of db records that contain that letter. Selecting on of the displayed items will populate the rest of the boxes.

The Error you mentioned just started in the wb I sent you. I can't figure out why. I have saved versions after making changes and never had that problem. I am sorry.

I just spotted that the command module does not contain the _click in the private procedure. That may not fix the problem.

[June7]

I am confused. I am not missing _Click anywhere as far as I can tell unless you mean the form procedures.

Why is there a Sheet11 module that duplicates code in Sheet1?

Combobox works as you instructed but it is annoying that the list doesn't populate with dropdown click.

Maybe you should replace the posted file.

Code would be easier to follow with proper indentation.

[StudentJacks]

I will try to fix what I can and try to post an updated wb maybe tomorrow evening. Thanks very much.

[StudentJacks]

I will try to fix what I can and try to post an updated wb maybe tomorrow evening. Thanks very much.

I have done some work in an effort to help figure out the problem.
First I created a completely new form (Userform1) created all the objects contained on the uf1 form and ran it. It opened as it should the first time but from then got an error on this procedure


Private Sub OpenAddModDelForm_Click()
uf1.Show
End Sub

So I tried changing the .Show to .Open, ran it and with the error changed the .Open back to .Show and it ran fine but the next time trying to run the code, it errors again.

The next thing I did was go into VBA, opened Userform1 and clicking the run command. The two procedures

Private Sub UserForm_Initialize()
ClearForm
LoadListBox
End Sub

work and run. You will get an error in the Sub LoadListBox() procedure.

At least you will be able to see the code in the Set tblList = ws.ListObjects("Table1") line of the code.

Hope this all makes sense.

[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.

[StudentJacks]

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

[June7]

Yes, it works with the Table2 I created. I just don't know if I based the table on correct range. So you just need to set up Table2 in your file.

[StudentJacks]

Yes, it works with the Table2 I created. I just don't know if I based the table on correct range. So you just need to set up Table2 in your file.

Thank you. Is there any chance you can provide the db that you worked on so I can view and test it? I still get compile errors and am going crazy?

Secondly, can you please tell me how many records were in the MasterData worksheet you last downloaded?
It is an important question and I would appreciate a response. Thank you

[June7]

I see 5 records.

All I did was create Table2. Didn't change any code. Table2 is what code referenced, however, I see your post shows Table1. Regardless, neither were in the Name Manager list.

If you want to see what I have, just download from your post.

[StudentJacks]

Thank you. Is there any chance you can provide the db that you worked on so I can view and test it? I still get compile errors and am going crazy?

Secondly, can you please tell me how many records were in the MasterData worksheet you last downloaded?
It is an important question and I would appreciate a response. Thank you

Well, surprise, I got the form working and it loads up with the MasterData records. I created a new Named range (TableX), updated the Table1 and/or Table2 to this new reference. Clicking the command button on the Find page (and it is there) will open the form without errors. But, if you click on one of the records in the forms list, you will see an error on the Set tbl1 = ws.ListObjects.Name = "TableX". This is the same error I was first trying to correct. Is it a syntax error?

If you are still willing to look at this ---- thank you is not enough but that's the best I can do.

This is the wrong file. I will provide the latest update in another post

[StudentJacks]

Use this one. I couldn't figure out how to remove the wrong one from the previous post

[StudentJacks]

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.

[June7]

Yes, the button is there and works.

This line:

Set tbl1 = ws.ListObjects.Name = "TableX"

should be

Set tbl1 = ws.ListObjects("TableX")


TableX is pulling only 4 columns. Sub txtbox1_AfterUpdate() expects 6. Resize TableX to include columns E and F. Include G if you want Notes and then modify code because it is loading email in the Notes textbox. Better add a textbox for Email. Modify code for 7 columns.


Still annoying that the combobox list won't load on click and it doesn't stay loaded.
URL textbox on form won't work as a clickable hyperlink. A textbox in Access will.
The hyperlinks also lose clickability in the VLookup() formula.


Why do you have combobox on worksheet instead of just using the form as interface?

[StudentJacks]

Yes, the button is there and works.

This line:

Set tbl1 = ws.ListObjects.Name = "TableX"

should be

Set tbl1 = ws.ListObjects("TableX")


Also, TableX is pulling only 4 columns. Sub txtbox1_AfterUpdate() expects 6. Resize TableX to include columns E and F.


Why do you have combobox on worksheet instead of just using the form as interface?

I was trying to try to filter to find a record in a timely fashion instead of scrolling through 300+ records to find the one I was looking for. I also thought this would be the fastest way to copy the Username & PW to paste on the websites logon page.

Thank you so much for your patience and expertise. Your solution worked and everything is working as I hoped. Happy Holidays to you and your family.

[June7]

I made a number of edits to previous post, probably while you were reading. Might review again.

Glad it helped.

Enjoy your holidays.

[StudentJacks]

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:


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?

[StudentJacks]

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 internet VBA codes and programs to remove the TableX named range but none of them work. In fact, the only range names the programs show are the DropDownList and the Table2 names. None even show the TableX name as being there. Hmmmm.

[June7]

Why delete the TableX, just resize its range. https://support.microsoft.com/en-us/office/resize-a-table-by-adding-or-removing-rows-and-columns-e65ae4bb-e44b-43f4-ad8b-7d68784f1165#:~:text=You%20can%20use%20the%20Resize%20command%20in%20Excel, range%20you%20want%20for%20your%20table%2C%20press%20OK.

Otherwise convert then delete https://support.microsoft.com/en-us/office/convert-an-excel-table-to-a-range-of-data-0b326ff1-1764-4ebe-84ea-786265d41c77

[StudentJacks]

Why delete the TableX, just resize its range. https://support.microsoft.com/en-us/office/resize-a-table-by-adding-or-removing-rows-and-columns-e65ae4bb-e44b-43f4-ad8b-7d68784f1165#:~:text=You%20can%20use%20the%20Resize%20command%20in%20Excel, range%20you%20want%20for%20your%20table%2C%20press%20OK.

Otherwise convert then delete https://support.microsoft.com/en-us/office/convert-an-excel-table-to-a-range-of-data-0b326ff1-1764-4ebe-84ea-786265d41c77

[StudentJacks]

Thank you so much. I did not know that the option to convert (resize table) as available.