Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > very very urgent vba pogram doubt
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default very very urgent vba pogram doubt
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

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 770
Default very very urgent vba pogram doubt
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

"sarasa " wrote in message
...
I have code below:
when i enter all field and press addrecord it does not store in
workbook second record. instead, first record erased and stored second
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 Sub


---
Message posted from http://www.ExcelForum.com/



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default very very urgent vba pogram doubt
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

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default very very urgent vba pogram doubt
Doug Glancy wrote:
[b]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

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default very very urgent vba pogram doubt
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


---
Message posted from http://www.ExcelForum.com/

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 770
Default very very urgent vba pogram doubt
Sarasa,

Did you change it to:

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

?

Otherwise I have no more help to offer.

Sorry,

Doug Glancy

"sarasa " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default very very urgent vba pogram doubt
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

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default very very urgent vba pogram doubt
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

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
doubt nibu Excel Worksheet Functions 2 August 15th 08 05:57 PM
if condition doubt deen Excel Worksheet Functions 2 May 2nd 08 06:14 PM
Excel Doubt Vasu Excel Discussion (Misc queries) 4 January 29th 08 01:39 AM
I have a doubt! Jaleel Excel Discussion (Misc queries) 4 September 21st 06 01:51 PM
some doubt mango Excel Worksheet Functions 6 December 31st 04 01:42 PM


All times are GMT +1. The time now is 04:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement