MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds


  Asked By: Thelma    Date: Aug 21    Category: MS Office    Views: 1725

I have a Userform. simple.

I have four OptionButtons (OptionButton1, OptionButton2, etc.)
On each I have it as:
PrivateSub Userform_Activate()
OptionButton1.Caption = ActiveSheet.Cells(1, 1).Value
OptionButton2.Caption = ActiveSheet.Cells(2, 1).Value
End Sub

I have Data in cells A1 thru A4

In Cell:
B1 = 0
B2 = 1
B3 = 0
B4 = 0

I have a Label or Textbox (Label1 or Textbox1)

And I have a CommandButton (CommandButton1)

What I want, Is if one of the option buttons are selected, and I
click on CommandButton1, that it checks Cells B1 thru B4 for the 1
and tells me if the option I selected is correct or not...
For Instance:
If I click option1 (b1) and click the command button, and the 1 is in
cell b2, then I want one of the labels... Either Label1 or TextBox1
to display "Incorrect."
If option 2(B2) is selected and cell B2 contains the "1", then I want
Label1 or textbox1 to display Correct, right then and there...

What I haven't figured out yet, is how to update the Label1.text or
Textbox1.caption when I hit CommandButton1.

Also: (not to ask for too much...)

I also have a NEXT button on the user form. the next button
(CommandButton2) Tells the OptionButtons to change text from cells
A1:A4 to A6:A9

Or to go down two cells and display the next four on the
Optionbutton1.caption =

If you havent figured it out yet, I am just making a simple Quiz with
four options. I know I can make this a lot easier if I use a
different program, but I would like to learn VB Code... It's always
something I've wanted to do.

Also I need to hide the Speadsheet behind the userform when I click
on a CommandButton embeded into the spreadsheet to show the userform

Also on top of that, When I hit the "Close" CommandButton

PrivateSub onCommandButton_Click()
Unload Me
End Sub

I need the speadsheet that I hid earlier to be displayed again.



4 Answers Found

Answer #1    Answered By: Ujala Hashmi     Answered On: Aug 21

To change  the text  of a text box

For a label  use

To make you Next button  work, you need to start off with a variable
Public StartRow as Integer. This should be in the form  declarations area

Then in your Userform initialize code
StartRow = 1

Define the option  buttons as
OptionButton1.Caption = ActiveSheet.Cells(StartRow, 1).Value
OptionButton2.Caption = ActiveSheet.Cells(StartRow+1, 1).Value

Your next button then has an on_click macro which contains
StartRow=StartRow + 5
Me.Parent.Repaint (I think this is right and may be needed to cause the new
options to be displayed)
Then you might want to say
If StartRow>MaxRows then
(Do something because the quiz has been completed)
End If

For hiding sheets you can use

Worksheets("Sheet1").Visible = False

To unhide
Worksheets("Sheet1").Visible = True

Answer #2    Answered By: Robin Bailey     Answered On: Aug 21

Through experimentaion and other sources, I have made my
userform quiz work exactly how I wanted  it to work thru the code.
As soon as I fix one bug, I will post my spreadsheet  on here and see
how people like it.

Unfortunatly, The Worksheets("sheet1").visible = false (etc.) does
not work for my excel, It keeps giving me the error: Cannot set all
sheets hidden, or something similar.

Is there any other code  anyone can think of to either minimize or
hide the speadsheet while the userform  is active?

I've also tried activeworkbook.worksheets("sheet1").visible =
xlveryhidden, and that does not work either.

Answer #3    Answered By: Oscar Evans     Answered On: Aug 21

I used:

Application.Visible = False

Answer #4    Answered By: Michael Evans     Answered On: Aug 21

How about showing the form  on an empty sheet with the
grid lines turned off?

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