very very urgent vba pogram doubt

S

sarasa

I have code below:
when i enter all field and press addrecord it does not store i
workbook second record. instead, first record erased and stored secon
value. could you any body help me 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 = "Question 1. " + OptionButton1.Caption
Else
If OptionButton2.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton2.Caption
Else
If OptionButton3.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton3.Caption
End If
End If
End If

If OptionButton4.Value = True Then
LastRow.Offset(2, 1).Value = "Question 2. " + OptionButton4.Caption
Else
If OptionButton5.Value = True Then
LastRow.Offset(2, 1).Value = "Question 2. " + OptionButton5.Caption
Else
If OptionButton6.Value = True Then
LastRow.Offset(2, 1).Value = "Question 2. " + OptionButton6.Caption
End If
End If
End If

If OptionButton7.Value = True Then
LastRow.Offset(3, 1).Value = "Question 3. " + OptionButton7.Caption
Else
If OptionButton8.Value = True Then
LastRow.Offset(3, 1).Value = "Question 3. " + OptionButton8.Caption
Else
If OptionButton9.Value = True Then
LastRow.Offset(3, 1).Value = "Question 3. " + OptionButton9.Caption
Else
If OptionButton10.Value = True Then
LastRow.Offset(3, 1).Value = "Question 3. " + OptionButton10.Caption
Else
If OptionButton11.Value = True Then
LastRow.Offset(3, 1).Value = "Question 3. Other"
LastRow.Offset(3, 3).Value = TextBox1.Text + " Months"

End If
End If
End If
End If
End If
If OptionButton11.Value = False Then
LastRow.Offset(3, 3).Value = " "
End If



LastRow.Offset(4, 1).Value = "Question 4. " + TextBox2.Text
LastRow.Offset(4, 2).Value = "Physician: "
LastRow.Offset(4, 3).Value = " " + TextBox2.Text

LastRow.Offset(5, 2).Value = " NP/RN: "
LastRow.Offset(5, 3).Value = " " + TextBox3.Text

LastRow.Offset(6, 2).Value = " Other: "
LastRow.Offset(6, 3).Value = " " + TextBox4.Text

LastRow.Offset(7, 2).Value = " Total: "
LastRow.Offset(7, 3).Value = " " + TextBox5.Text

LastRow.Offset(4, 5).Value = " Salary: "
LastRow.Offset(4, 5).Value = " " + TextBox6.Text

LastRow.Offset(5, 4).Value = "Operational: "
LastRow.Offset(5, 5).Value = " " + TextBox7.Text

LastRow.Offset(6, 4).Value = " Capital: "
LastRow.Offset(6, 5).Value = " " + TextBox8.Text

LastRow.Offset(7, 4).Value = " Total: "
LastRow.Offset(7, 5).Value = " " + TextBox9.Text



LastRow.Offset(8, 1).Value = "Question 5. " + TextBox10.Text

End Sub


Private Sub CommandButton2_Click()
End

End Sub

Private Sub CommandButton3_Click()

TextBox1.Text = " "
TextBox2.Text = " "
TextBox3.Text = " "
TextBox4.Text = " "
TextBox5.Text = " "
TextBox6.Text = " "
TextBox7.Text = " "
TextBox8.Text = " "
TextBox9.Text = " "
TextBox10.Text = " "
OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False
OptionButton4.Value = False
OptionButton5.Value = False
OptionButton6.Value = False
OptionButton7.Value = False
OptionButton8.Value = False
OptionButton9.Value = False
OptionButton10.Value = False
OptionButton11.Value = False

End Sub

Private Sub Frame5_Click()

End Sub

Private Sub Label4_Click()

End Sub

Private Sub Label5_Click()

End Sub

Private Sub UserForm_Click()

End Su
 
D

Doug Glancy

Sarasa,

I think if you change the line:
Set LastRow = Sheet1.Range("a65536").End(xlUp)
to:
Set LastRow = Sheet1.Range("B65536").End(xlUp)
it might work. Your code is looking in column A for the last row, but it's
always row 1 in Column A. Changing it to column B would make the last row
increment correctly, maybe.

Also, you should change the series of If statements:

If OptionButton1.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton1.Caption
Else
If OptionButton2.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton2.Caption
Else
If OptionButton3.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton3.Caption
End If
End If
End If
to:
If OptionButton1.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton1.Caption
ElseIf OptionButton2.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton2.Caption
ElseIf OptionButton3.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton3.Caption
End If

and so on for the other groups.

hth,

Doug Glancy
 
C

Charles

sarasa,

What are you trying to do? If you could attach your spreadsheet the wa
you want the information to appear. It would help.

Charle
 
S

sarasa

Doug said:
Sarasa,

I think if you change the line:
Set LastRow = Sheet1.Range("a65536").End(xlUp)
to:
Set LastRow = Sheet1.Range("B65536").End(xlUp)
it might work. Your code is looking in column A for the last row
but it's
always row 1 in Column A. Changing it to column B would make th
last row
increment correctly, maybe.

Also, you should change the series of If statements:

If OptionButton1.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton1.Caption
Else
If OptionButton2.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton2.Caption
Else
If OptionButton3.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton3.Caption
End If
End If
End If
to:
If OptionButton1.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton1.Caption
ElseIf OptionButton2.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton2.Caption
ElseIf OptionButton3.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton3.Caption
End If

and so on for the other groups.

hth,

Doug Glancy

Hi Doug,
I tried your code. the same problem if i try add record it woul
store second field instead of that it would store the first recor
itself.could you give the code which secord record thrid recor
stored in differnce cell. And explain to me scroll bar code also
Click to expand...
 
S

sarasa

hiall,
coudl you help me in this code how do i change set lastrow= Sheet1.
Range("b65536") End(x(up))
so on incremently so that first record second reocrdso on stroed in
different cell. Coudl you help me how to do it?
Explain to me i want to insert scroll bar. for that i need code where i
have insert the 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 = "Question 1. " + OptionButton1.Caption
ElseIf OptionButton2.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton2.Caption
ElseIf OptionButton3.Value = True Then
LastRow.Offset(1, 1).Value = "Question 1. " + OptionButton3.Caption
End If



If OptionButton4.Value = True Then
LastRow.Offset(2, 1).Value = "Question 2. " + OptionButton4.Caption
ElseIf OptionButton5.Value = True Then
LastRow.Offset(2, 1).Value = "Question 2. " + OptionButton5.Caption
ElseIf OptionButton6.Value = True Then
LastRow.Offset(2, 1).Value = "Question 2. " + OptionButton6.Caption
End If
If OptionButton7.Value = True Then
LastRow.Offset(3, 1).Value = "Question 3. " + OptionButton7.Caption
ElseIf OptionButton8.Value = True Then
LastRow.Offset(3, 1).Value = "Question 3. " + OptionButton8.Caption
ElseIf OptionButton9.Value = True Then
LastRow.Offset(3, 1).Value = "Question 3. " + OptionButton9.Caption
ElseIf OptionButton10.Value = True Then
LastRow.Offset(3, 1).Value = "Question 3. " + OptionButton10.Caption
ElseIf OptionButton11.Value = True Then
LastRow.Offset(3, 1).Value = "Question 3. Other"
LastRow.Offset(3, 3).Value = TextBox1.Text + " Months"

End If

If OptionButton11.Value = False Then
LastRow.Offset(3, 3).Value = " "
End If

LastRow.Offset(4, 1).Value = "Question 4. " + TextBox2.Text
LastRow.Offset(4, 2).Value = "Physician: "
LastRow.Offset(4, 3).Value = " " + TextBox2.Text

LastRow.Offset(5, 2).Value = " NP/RN: "
LastRow.Offset(5, 3).Value = " " + TextBox3.Text

LastRow.Offset(6, 2).Value = " Other: "
LastRow.Offset(6, 3).Value = " " + TextBox4.Text

LastRow.Offset(7, 2).Value = " Total: "
LastRow.Offset(7, 3).Value = " " + TextBox5.Text

LastRow.Offset(4, 5).Value = " Salary: "
LastRow.Offset(4, 5).Value = " " + TextBox6.Text

LastRow.Offset(5, 4).Value = "Operational: "
LastRow.Offset(5, 5).Value = " " + TextBox7.Text

LastRow.Offset(6, 4).Value = " Capital: "
LastRow.Offset(6, 5).Value = " " + TextBox8.Text

LastRow.Offset(7, 4).Value = " Total: "
LastRow.Offset(7, 5).Value = " " + TextBox9.Text



LastRow.Offset(8, 1).Value = "Question 5. " + TextBox10.Text

End Sub


Private Sub CommandButton2_Click()
End

End Sub

Private Sub CommandButton3_Click()

TextBox1.Text = " "
TextBox2.Text = " "
TextBox3.Text = " "
TextBox4.Text = " "
TextBox5.Text = " "
TextBox6.Text = " "
TextBox7.Text = " "
TextBox8.Text = " "
TextBox9.Text = " "
TextBox10.Text = " "
OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False
OptionButton4.Value = False
OptionButton5.Value = False
OptionButton6.Value = False
OptionButton7.Value = False
OptionButton8.Value = False
OptionButton9.Value = False
OptionButton10.Value = False
OptionButton11.Value = False

End Sub

Private Sub Frame5_Click()

End Sub

Private Sub Label4_Click()

End Sub

Private Sub Label5_Click()

End Sub

Private Sub UserForm_Click()

End Sub
 
D

Doug Glancy

Sarasa,

Did you change it to:

Set LastRow = Sheet1.Range("B65536").End(xlUp)

?

Otherwise I have no more help to offer.

Sorry,

Doug Glancy
 
C

Charles

sarasa


To set the scroll bar on your userform. Select userform Properties
Select ScrollBar and to the right click the down arrow and select "3"
This will place scroll bar on your form.


Charles
 
S

sarasa

I canged this code . but it does no increment. instead offirst recor
second record stored in the sam eplace.
what to do.

Set LastRow = Sheet1.Range("b65536").End(xlUp)


and another point in userform properties i cahnged scrollbar 3but i
doe not work
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vba program clarification 1
entering data into multiple sheets 9
make a data entry into hyperlink using vba? 3
Data Entry for 50 Rows only 2
excel vba programme 3
userform question 3
Formatting a row of Cells 1
adding border from userform 1

Top