MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Experimenting with user forms

  Asked By: Ashley    Date: Mar 03    Category: MS Office    Views: 1521

As a learning exercise I was trying to get the code sample posted at
working. This article is an excerpt from Microsoft Office Excel 2003
Programming Inside Out
from Microsoft Press (ISBN 0-7356-1985-9, copyright Microsoft Press
2004, all rights reserved). The authors of the book are Curtis Frye,
Wayne S. Freeze, and Felicia K. Buckingham.

The write-up is excellent but the sample has refused to work so far and
I'm not seeing anything obvious.

I have 37 columns of data and was hoping to use this style of forms to
display all the data on one screen instead of scrolling.

Using MS Excel 2002 with SP3 on XP Pro with SP2



3 Answers Found

Answer #1    Answered By: Dot net Sachin     Answered On: Mar 03

Can you possibly be a bit more specific than "the sample  has refused  to
work so far"please?

Are there any error messages? What happens that shouldn't ... what doesn't
happen that should ... do you see any smoke coming out of the keyboard ;- )

There's quite a bit of code  at the link you gave ... Which bit are we
talking about? How have you adjusted it for your application? Have you tried
stepping through the code and checking variable values or which bits of code
are being executed?

Answer #2    Answered By: Renee Lane     Answered On: Mar 03

There are two examples on the page - I was working on trying to see how
the first example titled "Capturing Information" worked.

I'm trying to work  with an exact copy of the example. No plans to change
any variable names or data  sets until I have a working reference

To be more specific about the error; in the section "displaying data" -
Private Sub GetData() seems to be the offending routine. If the textbox
is pre-loaded with 2 and you type a new number to jump to a new row in
the 2-20 range, then you get the Illegal Row Number msgbox. And none of
the database txtboxes fill with the data.


Answer #3    Answered By: Volney Fischer     Answered On: Mar 03

To get to the bit of code  that displays the message, r must be either < 2 or
> lastrow.

What's the value of r?

You can find this out several ways but the best is probably to set a

Position your cursor on the line
If r > 1 And r <= LastRow Then
And press  F9
Single Click in the grey margin to the left of the window at that line.

A red dot should appear and the line should be highlited red.
This indicates that a breakpoint has been set.

Run the application.

The code will pause at the breakpointed line and you can interogate r for
it's value.
If you "hover" over r it's value should be shown.
You can also set a watch on r.
Left click on r and select add watch.
Click OK and a watch window will come up showing the value of r.

It's generally better to fully name variables and even better to somehow
indicate their type.
I personally would name r as lnglRowNumber
This would tell me it was a Long, a Local variable and give information
about what it represents.
Likewise, LastRow would become cgLastRow indicating a constant that is

Please do get back if you know the value of r and are still getting stuck.

Didn't find what you were looking for? Find more on Experimenting with user forms Or get search suggestion and latest updates.