MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

User Form

  Asked By: Nisha    Date: Mar 23    Category: MS Office    Views: 867

I have designed a form that has text boxes in main frame for customer
name and account number. I then have 3 other frames that have a
further 2 text boxes and 3 radio buttons in each frame. The 3 frames
need to be linked to 3 separate sheets. If I input numbers to any of
the framed text boxes I want the customer name/account to appear on
the appropriate sheet. Dependent on which text box and which radio I
select I'd like to have a particular column populated (next available
row which I can probly do with isactive cell empty).

Question is how do i get this to work? Any help/ideas to help me along



7 Answers Found

Answer #1    Answered By: Seth Anderson     Answered On: Mar 23

You don't link frames  or other controls to sheets, you write code for the
individual controls that does whatever you want it to, including putting
information into worksheets of your choosing.

In the design environment, double-click on the control you want to process,
then write the code to handle the information from that control.

This can include accessing a sheet  by name. E.g.

Option Explicit

Private Sub TextBox1_Change()
Worksheets("Jim").Range("A1").Value = TextBox1.Value
End Sub

Answer #2    Answered By: Jeanne Lawson     Answered On: Mar 23

It's not clear to me exactly what is going on here. In any case, it
sounds as if you should define a bunch of constants and then just go
through the logic of each textbox/option button combination as to
where to put whatever you're putting.

constants would be like:


and logic like:

'assuming txbxBoxName1 is in Frame1
'assuming that you've checked that txbxBoxName1.Text has a number
'assuming that you've already found the row to put the data in

If Not IsEmpty(Me.txbxBoxName1.Text) And Me.opbtButtonName1.Value Then
TheCell=TxBx1OpBt1Col & TheRow
Workbooks(TheBook).Sheets(Frame1SheetName).Range(TheCell).Value= _
WhateverYouArePuttingHere 'e.g., Me.txbxCustomerName.Text
End If

My preference to 'do stuff' like this is after a CommandButton has
been pressed, so I'd add a CommandButton to the Form and put the code
in it. You'd might want to validate data entry in the TextBox_Change

Answer #3    Answered By: Reamonn Fischer     Answered On: Mar 23

Thanks for replies guys, will give some of the ideas a go at work
next week.

Just one more Q (for now anyways) - how do I get the textbox entry
(numeric) to pass to sheet  as a negative (This would be as a result
of selecting one of two other option buttons  on the main form  (ie
one for credit & one for debit)

Answer #4    Answered By: Aaron Evans     Answered On: Mar 23

Negation is simply, e.g., -Me.txbxCredit.Text, if the text  is a
number. You might want to use a condional like

If IsNumeric(Me.txbxCredit.Text) Then 'whatever

to prevent runtime errors.

Answer #5    Answered By: Salvatore Kelly     Answered On: Mar 23

Thanks for help  so far - as I said I am a real beginner and trying
to get to grips with VBA(not going so well have to admit). I know
I'm probly tryna run b4 I can walk but I need to get this form
working in the next 6 weeks. I have several tutorials but none seem
to go in to this much depth working with userforms.

I am getting really confused and would appreciate some more pointers

I have 3 frames  that each have a text  box in and 3 option buttons  A,
B & C

The idea is that whatever I input  in the 'frame' text box  and
depending on which option button is selected it will populate the
column in that spreadsheet & next available row.

I have 3 commandbuttons (ADD, CANCEL & CLEAR - which are working
(apart from the ADD obviously).

I also need to populate each spreadsheet with customer  Name & Account
from text boxes  in the main  form.i.e. if i only select one frame
input it only updates one sheet  with 'frame' input and customer
details BUT if use 2 or 3 it will populate 2 or 3 sheets  with frame
input and customer details

Customer Account Date (today)
Text Box Text Box Text Box
Frame 1 Text Box optbA optbB optbC
Frame 2 Text Box optbA optbB optbC
Frame 3 Text Box optbA optbB optbC

Answer #6    Answered By: Deloris Harris     Answered On: Mar 23

I'm not sure how "deep" you need to go for your training.
If you send me the .xls file that you have so far, I can clean it up
and perhaps record a tutorial that contains the steps to do what you're
looking for.

Answer #7    Answered By: Luisa Fischer     Answered On: Mar 23

If I select the fund from the combo box  – whatever I put in the £ or shares text
box (and depending on which option button within the same frame  is selected) it
will populate the same sheet  and the correct column.

I need to do is make sure the option button ‘rep’ negates the entries.

At the moment if neither the rep or sale button is selected it defaults to sale.
Need user  to select one or the other

I also need to do is build more frames  (about 10) in the form  so I can do
multiple entries for a customer  for different funds and classes. Eg use 1st
combo box to input  UKI entry in class B & 2nd for HIF entry in class C etc –
the idea is that the customer details will not need to be retyped every time
when there are multiple transactions.

Obviously sales will need to be entered separately from reps.

I also need to force the user to select one of the 6 option buttons  in the

I need to make the ‘£’ entry to 2 decimal places and the ‘shares’ entry to 3 DP

I also need to automatically make the date field default to the entry date when
it populates the relevant sheet entry (maybe make it so the user can’t change
that field).

I also need to make the frame text  boxes accept numbers  only. The name box needs
to accept string only & the account numbers and string.

Then I need to add up the columns £s and shares for each class and transfer to a
summary page.

I suspect there are other little validations I need but if you could sort some
of these out and explain how they work  it would be extremely helpful

Didn't find what you were looking for? Find more on User Form Or get search suggestion and latest updates.