User Forms - getting them to talk to Excel

[Amber_D_Laws]

Hello Again,

As some of you already know, I have been working on a quote module for
my company. After extensive gnashing of teeth, and pulling of hair as I
fought with several coding problems, I have completely ditched the first
try, and I am now on round two.

I realized most of my problems were stemming from my trying to prevent
my users from being able to unintentionally mess things up, while
keeping the whole thing friendly to those that are not friendly with
computers.
Through all my research, it finally dawned on me that I need to be
using a series of user forms.

User forms seem easy enough, and so far making it look like I want
isn't a problem at all. Figuring out how to get it to put the
information into Excel the way I want has been. This leads me to the
first two questions in what is likely to be a long series of questions
in the future.

1. What code do I use to sequentially have the user go through the
user forms in the order I want them to.

And

2. I have a list of client names with their corresponding contact
information in a combo box, so my users are picking the client they
want to quote from the drop down list. Because we frequently quote new
clients, I wanted a way to give them an option to add a new client. No
problem. I found the code to add them to the list, and it works!
Hurray
The question is, how do I expand the row source to reflect the new
addition/s, and how do I keep the users from adding duplicate clients.

Any advice will be helpful.

Thanks in advance to everyone who reads or posts to this tread.
Amber

 

[Tom Ogilvy]

Userform1.show
Userform2.show
Userform3.show

you show the forms.

Use a defined name for the rowsource

When you add a row, redefine the named range and reset the rowsource.

Worksheets("Sheet1").Range("A1").currentRegion.Name = "Table1"

You don't have to delete the name if it already exists, this redefines it.

Dim res as Variant
res = application.Match(Textbox1.Text,Range("Sheet1!A:A"),0)
if not iserror(res) then
msgbox TextBox1.Text & " already exists"
Textbox1.Text = ""
exit sub
End if

Some additional sources of information:



http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data


http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data


http://j-walk.com/ss/excel/tips/tip84.htm


See this tutorial here
http://www.dicks-blog.com/excel/2004/09/linking_userfor.html


http://support.microsoft.com/default.aspx?scid=kb;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel




XL97: How to Use a UserForm for Entering Data (Q161514)
http://support.microsoft.com/?id=161514


XL2000: How to Use a UserForm for Entering Data (Q213749)
http://support.microsoft.com/?id=213749


Here are some other sources of information:


http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.


How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;829070


http://support.microsoft.com/?id=168067
File Title: Microsoft(R) Visual Basic(R) for Applications Examples for
Controlling UserForms in Microsoft Excel 97
File Name: WE1163.EXE
File Size: 161742 bytes
File Date: 05/08/97
Keywords: kbfile
Description: This Application Note is an introduction to manipulating
UserForms in Microsoft Excel 97. It includes examples and Microsoft Visual
Basic for Applications macros that show you how to take advantage of the
capabilities of UserForms and use each of the ActiveX controls that are
available for UserForms


Peter Aiken Articles:
Part I
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/Introductiont...
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/Introductiont...



--
Regards,
Tom Ogilvy


"Amber_D_Laws" <[email protected]>
wrote in message

 

[JakeyC]

A lot to answer, so a brief starting point for you:

1. It sounds like you might benefit from using a MultiPage control on
a single form - see VBA help for more info on how they work. If you
really do need separate forms, then perhaps use the Deactivate event of
one Form to call .Show method of the next.

2. Use something like:

lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
ComboBox1.RowSource = "A1:A" & lastrow

to set the ComboBox1 range to be the last row with data in column A on
Sheet1. Change according to where your actual data is!

As for avoiding duplicates, it would be handy if you had a set format
for clients' names to check - just in case "James Thompson LTD." gets
entered as "J Thompson LTD." in the future.

Have a look at Application.Find to help with searching for text etc.

 

[Amber_D_Laws]

WOW! Thanks for the great resources. I am leaving for the weekend, but I
will let you know how it goes on Monday.

Have a great weekend,
Amber

 

[Amber_D_Laws]

Hey Jakey,

Thanks for the ideas! I will keep both you and Tom posted as thing
progress.

I considered MultiPage Controls, but there are several steps, and
want my users to go through them in a precise order. It is m
understanding that a multipage has tabs, and the users would be able t
shift from one to the other, and in that way they might miss a step. Or
am I wronge. Admitidly I don't know a whole lot about user forms, s
please if I am off base let me know where my thinking strayed.

See ya' around,
Amber

 

[Amber_D_Laws]

The two sites in the parred down quote below did not work, and I got
404 error from the microsoft site when I tried to visit them.

I just thought you might like to know. All the rest pulled up fine.
have printed them, and will be reviewing them throughout the day.
will let you know as soon as I have something new to report.

Thanks again, and I hope you have a wonderful day,
Amber

...text omitted...

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.

...text omitted...

Peter Aiken Articles:
Part I
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/Introductiont...
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/Introductiont...

--
Regards,
Tom Ogilvy


------------------------------------------------------------------------ http://www.excelforum.com/member.php?action=getinfo&userid=30012
View this thread

http://www.excelforum.com/showthread.php?threadid=505875
[/QUOTE]

 

[JakeyC]

Design your MultiPage with tabs first so that you can easily switch
between pages. Then, when you are ready to give it to your users change
the Style propery of the MultiPage to '2 - fmTabStyleNone' which
removes the tabs. Make sure of course that you provide a button for
them to select the next page!

 

[Amber_D_Laws]

Thanks Jakey!

I am still working through the literature that Tom referred me to, an
I have just come across some details on MultiPages. I am glad yo
mentioned them; because other wise I don't know that I would hav
considered them. I like the idea about changing the tab style. Th
whole point of this is to keep my users from being able to change wha
they should not be allowed to change.

Maybe you can give me a little help with something along these lines.
Do you know how to add a status bar of some sort so they can see ho
close to completion they are?
I haven't found anything relating to this so far. I was thinking o
something like sequentially called images, but I am sure there is a
easier way.
Any ideas?


Regards,
Amber

 

[JakeyC]

Application.Statusbar will change the message in the bottom left of the
window. You could have a very crude one that starts at "0%
Completed..." then a quarter of the way through your code becomes "25%
Completed..." etc.

John Walkenbach has a nice graphical solution at
http://www.j-walk.com/ss/excel/tips/tip34.htm which might be of use.

 

[Amber_D_Laws]

Thanks for the tip. I will look into it when I really get to that stage

Hopefully you can help with this. Please see the code snipet below:


Code
-------------------
Dim SourceData As Range
Dim found As Object

Set SourceData = Range("Companies")
Set found = Nothing
'Try to find the value on the worksheet
Set found = SourceData.Find(txtCompanyName.Value)

'If item is not found in the list...
If found Is Nothing Then
'redefine Companies
SourceData.Resize(SourceData.Rows.Count = 1, 1).Name = "Companies"
'add the new item to the end of the list on the worksheet
SourceData.Offset(SourceData.Rows.Count, 0).Resize(1, 1).Value = txtCompanyName.Value
'Reset the list displayed in the ComboBox
txtCompanyName.RowSource = Range("Companies").Address(external:=True)
End I
-------------------


I am running into errors at the SourceData.Resize... point of thi
code. It says "Application-defined or Object-defined error".
Help!!! I got this code straight off of one of the sites Tom refered m
to, one of the microsoft sites no less. The only thing I changed was th
text for the named range. The one from online used ListRange, and I use
Companies. That shouldn't make a difference should it.

Thanks a heap in advance!

 

[Amber_D_Laws]

Bump to top of list..

 

[Amber_D_Laws]

Problem solved. There is an typo in the line
"SourceData.Resize(SourceData.Rows.Count = 1, 1).Name = "Companies""
the = in between Count and 1 should have been a +. However, now I have
a new error issue, and it does not seem typo related.


Code:
--------------------
'Reset the list displayed in the ComboBox
txtCompanyName.RowSource = Range("Companies").Address(external:=True)
--------------------


The error message is:

Could not set the RowSource property. Member not found.

Any ideas?

Thanks,
Amber

 

[Amber_D_Laws]

Nevermind. The code works fine now. Must have just been some sort o
glitch. We have been having PC issues around the office all morning.

I will close this tread now, and so I just want to send a big thank yo
to Tom for all the fabulous resources, and to Jakey for several grea
ideas and lots of encoragement.

See ya' soon, and take care,
Amber: