excel vba programme

[sarasa]

i have 10 questions. each question has 3 option button
if i select 1 option button for each quesiton it shoud store it in wor
sheet. but it does not. coudl you verify this code.

Private Sub CommandButton1_Click()

Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(xlUp)
If OptionButton1.Value = True Then


LastRow.Offset(1, 1).Value = OptionButton1.Caption
Else
If OptionButton2.Value = True Then
LastRow.Offset(1, 1).Value = OptionButton2.Caption
Else
If OptionButton3.Value = True Then

LastRow.Offset(1, 1).Value = OptionButton3.Caption
End If
End If
End If

LastRow.Offset(1, 0).Value = OptionButton.CaptionTextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text


MsgBox "One record written to Sheet1"

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
OptionButton1.Value = False
OptionButton2.Value = False





TextBox1.SetFocus

Else
Unload Me
End If

End Sub


Private Sub CommandButton2_Click()
End

End Sub

Private Sub UserForm_Click()

End Su

 

[Mark]

I think you have mistyped your code or you do not realise
that after the options, you have then inserted the text of
textbox2 into your target cell.

Let's say:

LastRow.Offset(1, 1).Value = OptionButton3.Caption

then you say
LastRow.Offset(1, 1).Value = TextBox2.Text

You have just overwritten the caption

regards
Mark

 

[Greg Wilson]

Point 1:-
Note the following two statements - the first contained
within the option button code and the second that follows.
The second obviously nullifies the first:

LastRow.Offset(1, 1).Value = OptionButton2.Caption
LastRow.Offset(1, 1).Value = TextBox2.Text

Point 2:-
Also note that your use of nested If/Then code is
unnecessary. I suggest using an If/ElseIf/Else construct
instead. It's much simpler. I use this in my code example.

Point 3:-
I assume the following was a typo and does not exist in
your code:

LastRow.Offset(1, 0).Value =
OptionButton.CaptionTextBox1.Text

Point 4:-
You declare the LastRow variable as:

Dim LastRow As Object
Set LastRow = Sheet1.Range("a65536").End(xlUp)

Although not a critical point, since the code sets LastRow
to a cell reference, you should decalare it more precisely
as "Range" rather than "Object". Also, the variable name
seems to imply a row reference (Long or Integer) instead
of a cell (Range). In my code example, I use the variable
name "C" instead of "LastRow" which implies a cell
reference for readability purposes.

Point 5:-
You set the LastRow variable to the last used cell in
Column A and then go on to reference offsets of this
several times. I thought it more efficient to set the
variable reference (I use "C") to the offset of 1 row down
and 1 column right and thus reduce then need for offset
references.

I don't know exactly what you're trying to do so the
example that follows will not do entirely what you want. I
comment out the line "C.Value = TextBox2.Text". This was
the translation of your code that nullified the option
button code. I don't know what you want to do here. Give
it a shot and see where we get.

Private Sub CommandButton1_Click()
Dim C As Range
Dim Msg As String, Resp As Integer
Dim Style As Integer, Title As String

Set C = Sheet1.Range("A65536").End(xlUp).Offset(1, 1)
If OptionButton1 Then
C.Value = OptionButton1.Caption
ElseIf OptionButton2 = True Then
C.Value = OptionButton2.Caption
Else
C.Value = OptionButton3.Caption
End If
C.Offset(0, -1).Value = TextBox1.Text
'C.Value = TextBox2.Text
C.Offset(0, 1).Value = TextBox3.Text
Style = vbInformation
Msg = "One record written to Sheet1 "
Title = " Data entry"
MsgBox Msg, Style, Title
Msg = "Do you want to enter another record? "
Style = vbYesNo + vbQuestion
Resp = MsgBox(Msg, Style, Title)
If Resp = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
OptionButton1.Value = False
OptionButton2.Value = False
TextBox1.SetFocus
Else
Unload Me
End If
End Sub

Regards,
Greg

 

[Greg Wilson]

I had assumed that OptionButton3 was the default selection
since your code sets OptionButton1 and 2 to false before
making another entry. Since option buttons are mutually
exclsive, you can simplify it to just setting
OptionButton3 to True and not set the others to False. My
assumption that OptionButton3 was True by default may be
wrong and so my code may not do what you want.

Regards,
Greg