Excel VBA Form Coding Error

I hope that is sufficient information.

Oops, it appears that the module code that uses the form input was not uploaded. Here is a sample (I have to remove repeating coded to get withing the upload limit). The areas were the code has been removed is shown by the embedded comments.

Please Login or Register  to view this content.

Many thanks for the assistance given (so far). I have changed the AddName module code to rename the Name variable
to strName string variable. I have removed the second instance of Load AdNameForm and so do not get that error.

I would now be grateful for your assistance (from anyone) to fix another problem that is preventing my AddName
module code from running. I have attached an image of the line where the code fails (this is only a section of the full
AddName module code). The Forename and SURNAME variables are the names I have given to the forename and surname
text boxes in the AddNameForm form. When a members name is entered in the form I was expecting the Forename and SURNAME values
to be available in the AddNames module so that I could trim the values (to ensure there was no leading or trailing blanks)
and then concatenate the Forename and SURNAME to produce the strName variable.

The line of code that fails produces a 424 "Object Required" error message.

Thanks Guru, that worked. I still have to add some verification to the code (i.e. to ensure that both the surname and forename have been entered in the form).

The reason I did not embed the code in the userform's initialisation event was because I had already written the code in the AddName module when I was using the InputBox function to input the members' name. To extend my knowledge of Excel VBA I thought I would have a go at doing the same with a form which gives me more control of the look and verification of the input.

After completing the verification coding I intend to produce a form/module to allow names to be deleted by selecting a name form a drop-down list. I already have the module code for doing this using the InputBox function but this relies on the user typing in the name exactly - something that is prone to error. I may need your help again!!

Once again, many thanks.

I now want to modify coding in a module that uses a InputBox to input the name of a member so that it can be deleted from a
number of worksheets lists (of members). The Input box method is prone to typing errors and so I want the user to be able to select the
members name from a ListBox in a Form. I have produced the form (see image) and named the following in the form:

Frame: DeleteMember
Form: DeleteNameForm
ListBox Name: MemberList (set as a named range in list of members worksheet)
Button: DeleteNameButton


I have a module FIndAndDelete that works using the InputBox method. All I need to do is take the selected value from the DeleteNameForm and enter this as a
string value.

I want to obtain the selected value (the selected name) so that I can use in a module that finds the name in a long list of members and deletes the whole row.
I have tried the following code but this produces an error 438 "Object does not support this property or method".

Please Login or Register  to view this content.

Any advice would be most welcome.

Thanks for the responses. The code is in a module called FindAndDelete that I have use to enter a name, search for that name in 14 worksheets, and
then delete the row that contains the name. I currently use an InputBox function in the FindAndDelte module to enter the name but I want to use a form to select
the name from a ListBox in the DeleteNameForm to ensure that the correct name is selected (no chance of typing errors). The relevant section of code
using the InputBox method is as follows:

Please Login or Register  to view this content.

I want to use the DeleteNameButton click event to 'call' the FindAndDelete module. What code do I need in the DeleteNameForm?

How do I change the above module code to take the selected name from the form and make this the search string?

Thanks for your assistance. I want to call the existing FindAndDelete module from the Delete Name button in the DeleteNameForm rather than
place all the code in the DeleteNameForm click event area of code. I did a similar thing for adding names to many worksheets (Nov 2016,
Dec 2016 ........Oct 2017) and it worked (see first image). I want to do the same to find and delete a name but I get an error message
(see second image).

Once I get the button click event to work (calling the FindAndDelete module it should work (hopefully). Here is your code that I modified
which I think will work:

Please Login or Register  to view this content.

I forgot to add the images - here they are.

Thanks to you both - I now see the problem - the module and sub have the same name. When I used AlphaFrog's solution (Call FindAndDelete.FindAndDelete it worked. I will fix the confusion about module and subs by changing the name of the module. I am trying to learn how to use Forms in VBA and so all your help and advice has
been very useful.

It's me again!

I have tried for hours to get the above code to work but have failed to do so. Your further assistance would be much appreciated.

Here is the relevant section of code:

Please Login or Register  to view this content.

I have created the form shown in the attached image to 'call' the FindAndDeleteName2() subroutine of which the code above is part.

When I run the subroutine the DeleteNameForm form appears on the screen. I have designated the range for the list box as VetsNames in the Members worksheet which displays the names as shown
in the image but when I click on a name in the list nothing happens, the form remains on the screen and the only way to remove the form from the screen
it to click on the Cancel button (or the X).

The code for the "Delete Name" button on the form is shown below:

Please Login or Register  to view this content.

Thanks for responding. I deleted the two lines in the FIndAndDeleteName2 module and The Unload DeleteNameForm line from the DeleteNameButton_Click event. Now the form does not appear and I get the "No Name Selected Message'. I would have thought the DeleteNameForm would need to be shown to allow a name to be chosen from the MemberList box.

I left the main code in the FindAndDeleteName2() module because I have lots of other code in this module. I commented out the load and show DeleteNameForm
lines and added the DeleteNameForm.Show line to the button click event. The form now appears but when I click on the Delete Name button I get the following
error message:

Runtime Error 400. Form already displayed; can't show modally.

I did not understand your latest suggestion to move some of the module code to the click event (my problem) and so I gave up trying to call the delete name module from the click event and pasted all the module code into the click event as you suggested earlier. That worked!! Many thanks for your help.