MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Multi-tab form

  Asked By: Howard    Date: Nov 14    Category: MS Office    Views: 2157

I got through with my first form with some excellent help from
this group, which I really appreciate! Now I am trying to continue
on... what I need to do is make a multi-tab form... the form I
finished is the first tab of the form... I am trying to add the
second and have done so without issue...what I want to do now is
utilize the code I used for the navigation part of the first form...

Now to be clear, I had already done each of the 3 forms I needed as
stand alone forms and then decided to make it a multi-tab form... so
when I added the second tab, I copied the contents of the stand alone
form and pasted it to the second tab of the multi-tab form... Now
when I try to point the First record command click button to the same
name of the command click I used for the first tab of the multi-tab
form I get an error that its' an ambiguous name and cannot be saved...

How do I get around this?

Also, I would appreciate any recommendations on a good solid text
that will help me in my beginning steps with VBA...



15 Answers Found

Answer #1    Answered By: Clarence Nichols     Answered On: Nov 14

Some steps I would recommend:

Make sure every control has a different name. Rename any with names like
Button17 to btnOpenTheNewForm or something.

use the onclick event for the button to call a sub which is not an onclick for
another button. i.e. use the onclick subs only to call another sub or run a
one line command. Do not fill them with other stuff. This is called
Modularising your code.

Use separate modules for general code rather than putting it in the sheet or
form code modules.

If you structure your code in this way it will help  you to build and maintain
reusable code which saves a lot of time in the long run.

Answer #2    Answered By: Geena Ma.     Answered On: Nov 14

I am doing this and I believe I have overcome some of the
issues... I have run into another though... I have added the second
page of the multipage form  and have figured out how to identify and
activate the worksheet affiliated to each page of the multipage
form... but when I try to add the information captured on the second
page of the multipage form, I get an error that its'
an invalid record... I copied/updated code I used for the first
worksheet making it applicable to the second page of the multipage
form specifically. The code to add the information to the
spreadsheet is:

Public Function SubSiteSave()
Dim R As Long

If IsNumeric(RowNumber.Text) Then
R = CLng(RowNumber.Text)

MsgBox "Illegal row number"
Exit Function

End If

If R > 1 And R <= LastRow Then
strProtocolID = Worksheets("IVRSInfoSheet").Range("A2")
Cells(R, 1) = strProtocolID
Cells(R, 2) = cmboxSiteID.Text
Cells(R, 3) = txtSubSiteID
Cells(R, 4) = lstboxSalutation
Cells(R, 5) = txtPersonRcvDSRFirstName
Cells(R, 6) = txtPersonRcvDSRLastName
Cells(R, 7) = txtShipAddress1
Cells(R, 8) = txtShipAddress2
Cells(R, 9) = txtShipAddress3
Cells(R, 10) = txtShipAddress4
Cells(R, 11) = txtShipAddressCity
Cells(R, 12) = txtShipAddressState
Cells(R, 13) = txtShipAddressZip
Cells(R, 14) = txtShipAddressPhoneNumber
Cells(R, 15) = txtShipAddressFaxNumber
Cells(R, 16) = txtShipAddressEmailAddress
Cells(R, 17) = ComboBox1
Cells(R, 18) = ComboBox2
Cells(R, 19) = ComboBox3
Cells(R, 20) = ComboBox4


MsgBox "Invalid row number"

End If
End Function

I am sure the issue is that the R is not seen as >1 and <= LastRow....

Can you help  please?

Answer #3    Answered By: Garrett Brooks     Answered On: Nov 14

I don't like


These look like boxes which do not have specific names and could be duplicated
on the other pages of the form. Also I would recommend using .text or .value
at the end of each of those lines so you know what you are getting. However
that may not be the problem.

I am not clear what RowNumber is. There are no DIM statements. Is it DIMed
as an object? Does that object have a text property?

How about putting a breakpoint on the line
If R > 1 And R <= LastRow Then
and then (when it breaks) floating the mouse over the R to see what value it
has. Or type ?R in the immediate window.

Then step through the rest of the lines (f8) and see where it dies.

Hey, Why is this a function not a sub? Try making it a sub unless you need it
to be a function. (Again, I don't know that this is a problem)

Answer #4    Answered By: Willis Hill     Answered On: Nov 14

I am back again... I am using a multipage form... I am gathering
information for the form  then saving/updating the spreadsheets (one for
each tab of the multipage form). I have setup navigation buttons based
on click() commands... What I would like to do is setup the navivation
button coding to work on all 3 tabs of my form... I have not been able
to do this... I have had to setup navigation buttons for each tab and
this is increasing the overall size of the module...also it would be
better to have one set of navigation buttons from a coding aspect.... I
would appreciate any help  on this subject....

Answer #5    Answered By: James Williams     Answered On: Nov 14

I assume by "spreadsheets" you mean "worksheets" not "workbooks"?

I don't know what you mean by "I have had to setup navigation buttons for
each tab ..." Why? What's stopping you just defining a single set of
navigation buttons outside your MultiPage control (presumably below it, at
the bottom of the form)?

What specifically have you tried, that has failed? And what errors are you

Answer #6    Answered By: Albert Ellis     Answered On: Nov 14

I remembered one other thing I was having difficulty with the
navigation buttons... I am using a form  for each tab that collects
data then when I press the save command button it saves the data to
the active worksheet... Additionally, as I navigate the worksheet (go
to next row, previous, last, first, etc using the navigation buttons)
the form shows the data saved in that row of the worksheet or alerts
you to being at the last or first row of the worksheet... I can then
edit the data and either save it or cancel the save...

To navigate the worksheet, I have a text object called RowNumber.
RowNumber is used to identify the active row and then display the
data in that row on the form. Here is part of the code for
identifying where rows are:

Private Function FindLastRow()

Dim R As Long

R = 2
Do While R < 65536 And Len(Cells(R, 1).Text) > 0
R = R + 1


FindLastRow = R

End Function

FindLastRow is a function to identify the last row in the active
worksheet, LastRow is the last row on the worksheet that has data...

When I go to the next record say... I use:

Public Function NextRecord()
Dim R As Long

If IsNumeric(RowNumber.Text) Then
R = CLng(RowNumber.Text)

R = R + 1

If R > LastRow Then
MsgBox ("You have reached the end of the database")

ElseIf R > 1 And R <= LastRow Then
RowNumber.Text = FormatNumber(R, 0)

End If

End If

End Function

So RowNumber is updated with the row number from R....and that
identfies the row...

I have setup the form so that the navigation buttons are at the
bottom of the userform not on a specific tab. I cannot get the
RowNumber object to display the row for the active worksheet...I have
the following code to activate the worsksheet as I change form by
clicking on the tab of the multipage form:

Private Sub MultiPage1_Change()

If MultiPage1.Value = 0 Then
Set ws = Worksheets("SiteInfo")
LastRow = FindLastRow

ElseIf MultiPage1.Value = 1 Then
Set ws = Worksheets("SubSiteInfo")
LastRow = FindLastRow

ElseIf MultiPage1.Value = 2 Then
Set ws = Worksheets("CallerInfo")
LastRow = FindLastRow
End If

End Sub

How do I get the RowNumber object to take on the number of the
current worksheet? I would appreciate your insight... Hopefully its'
not too obvious...

Answer #7    Answered By: Dannon Jones     Answered On: Nov 14

I tried just using the navigation buttons across the tabs on the
multipage form  by just calling the sub/function with new command
button and a click event... but this resulted in errors
(mostly undeclared sub/functions). I had thought that I could setup
the navigation buttons at the bottom of the multipage form and then
use them across the 3 tabs I planned on creating... but I didn't know
if that required specific programming or setup.

I assume that as long as I setup the navigation buttons on the bottom
of the mutlipage form, I can then just identify the work sheet I am
using so that the buttons navigate the same no matter what worksheet
I am currently in... (I assume as long as I activate the correct
worksheet via the change tab event, right?) I also assume that for
the command buttons that add/save/cancel save for each worksheet that
I will need specific coding for those functions to work
appropriately, is this correct?

I really appreciate your assistance on this...

Answer #8    Answered By: Walborgd Fischer     Answered On: Nov 14

I don't know why your code isn't doing what you want, at a casual glance,
but I recommend you take a different approach to handling your worksheets.

Relying on "active sheet" is not a good idea. And in fact you have a
mixture of "active sheet" and "object" accesses to your sheets.

What I believe you should do is define as many WorkSheet variables as you
have worksheets. Right at the beginning, set each of these to be a
reference to their respective worksheets. (If you like, you could even
create an array of WorkSheet references, aligned with the MultiPage1.Value
values. Then you can just use the page number as an index into the
WorkSheet references array.)

Then, use these WorkSheet variables in dot notation for each reference to
Range, Cells, etc. For subroutines, pass the particular WorkSheet variable
as a parameter. Or possibly store it in a global "ws" variable.

Don't forget the With phrase, which you can use to avoid repeating the
WorkSheet reference over and over again.

Just scanning your code again, I can't see where you initialise RowNumber
when you change pages.

Answer #9    Answered By: Bradley Evans     Answered On: Nov 14

I appreciate your insight and suggestions... but as I am still very
much a novice at this, I am not sure how to do some of the items you
note below... When you say to establish the worksheets at the
beginning, I thought that's what I did with the Multipage1_Change()
coding...doesn't this identify the worksheet by name as I activate
the form  tab? If it does, am I having difficulty because, as you
indicate below I don't initialize RowNumber....

I would appreciate any suggestions on this and if you could I would
appreciate your suggestions on what VBA books might assist me...
currently I am using Excel 2003 Programming (Inside/Out). But I find
its' not that helpful...

Answer #10    Answered By: Barak Levi     Answered On: Nov 14

If you put the buttons on all the MultiPage pages, then they'll presumably
all need separate names and separate code. Probably not what you want to

You certainly should be able to put the navigations at the bottom and use
them for all pages.

Answer #11    Answered By: Rosalie Holmes     Answered On: Nov 14

You do set ws as a reference to the appropriate sheet, but then you don't
use it. All you do is we.Activate and then trust to luck that it stays
activated. You should make sure that you actually specify ws as a prefix to
every action that access a worksheet. Once you do that, you won't even need
the we.Activate unless you actually want to bring the sheet to the
foreground so you can see it yourself.

I'm not sure why you are using a cell to hold the row number in, rather than
a VBA variable. But if you aren't initialising it when you change pages,
presumably that will give you problems.

You seem to have a straight debugging problem here. Set breakpoints in your
code and step it through - paying attention to the values in the various
variables - particularly immediately after page changes.

Before you do too much debugging, customise the VB environment toolbars to
make sure you have icons for "step over", "step into", "step out" (of
current sub or function) and "run to cursor". They do have hot-keys, but
it's often easier to click on the toolbar, and most frustrating to try to
use them from the Debug menu item.

Also make sure that your have Option Explicit at the top of every module to
ensure that all of your variables are defined correctly and that typos don't
make trouble.

Answer #12    Answered By: Zachary Larson     Answered On: Nov 14

I did debugging and stepped through while I was waiting for your
response and I found RowNumber was not the textbox name... it was
SSRowNumber... once I changed this on all the navigation buttons,
then they started working fine. I already use option explicit to
ensure no typos, etc.... and I will go back through the code again
and if I understand you correctly in regards to the "ws." notation...
I have not used that, as I said I am still quite a novice... so if I
understand it correctly, then in the code say for saving data in the
PutData code, I would need to use Worksheets("CallerInfo").Cells(R,
1) = strProtocolID vice Cells(R, 1) = strProtocolID... is this

Private Sub PutData()

Dim R As Long

If IsNumeric(SSRowNumber.Text) Then
R = CLng(SSRowNumber.Text)

MsgBox "Illegal row number"
Exit Sub

End If

If R > 1 And R <= LastRow Then
strProtocolID = Worksheets("IVRSInfoSheet").Range("A2")
Cells(R, 1) = strProtocolID
Cells(R, 2) = txtSiteID.Text
Cells(R, 3) = StrConv(txtPIFirstName, vbProperCase)
Cells(R, 4) = StrConv(txtPILastName, vbProperCase)
Cells(R, 5) = txtPIPhoneNumber
Cells(R, 6) = txtPIFaxNumber
Cells(R, 7) = txtPIEmailAddress
Cells(R, 8) = txtMaxSiteScreenAmount
Cells(R, 9) = txtMaxSiteRandAmount

MsgBox "Invalid row number"

End If

Answer #13    Answered By: Russell Burns     Answered On: Nov 14

. or simply ws.Cells(R,1) if you have ws set to the current worksheet.

Answer #14    Answered By: Louis Mason     Answered On: Nov 14

Can you recommend a text/manual for
VBA? I usually get more when I can see/read the same information in
text form. I would appreciate a recommendation on a good all-around
text that will help  me in the beginning and perhaps later as I get
more confident and want to try more sophisticated coding.

Answer #15    Answered By: Hehet Chalthoum     Answered On: Nov 14

No, sorry. I've never used one. The auto-complete and the help  in the VB
environment are very useful; and Google will find lots of stuff for you.

And - after all - Basic is Basic. :-)

Didn't find what you were looking for? Find more on Multi-tab form Or get search suggestion and latest updates.