Help with Userform Code

**JessFace** · 07-27-2010, 09:43 AM

Hi All,

I have created a userform that is supposed to enter data into multiple worksheets depending on who a Meeting Attendee is (i.e. if the meeting attendee is Allan, when he is choosen from the drop down menu and the form is completed, that entry would go to a worksheet named Allan). But instead of going to the correct worksheet, it takes takes me that worksheet, but there is no data there.

Here is the code im using:

Private Sub cmdAdd_Click()
' set form to workbook

ActiveWorkbook.Sheets("AllTasks").Activate
Range("A2").Select

'find an empty cell

Do

If IsEmpty(ActiveCell) = False Then

ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form to worksheet

ActiveCell.Value = dateText.Value

ActiveCell.Offset(iRow, 1) = Me.txtArea.Value

ActiveCell.Offset(iRow, 2) = Me.cboMtgAtt.Value

ActiveCell.Offset(iRow, 3) = Me.cboStat.Value

ActiveCell.Offset(iRow, 4) = Me.cboPrior.Value

ActiveCell.Offset(iRow, 5) = Me.txtItem.Value

ActiveCell.Offset(iRow, 6) = Me.txtAct.Value

ActiveCell.Offset(iRow, 7) = Me.txtTgtDate.Value

ActiveCell.Offset(iRow, 8) = Me.txtCompDate.Value

ActiveCell.Offset(iRow, 9) = Me.txtObjt.Value

ActiveCell.Offset(iRow, 10) = Me.txtProj.Value

ActiveCell.Offset(iRow, 11) = Me.txtCmmts.Value

' add data to Allan Sheet _
if Meeting Attendee drop down menu equals Allan
If cboMtgAtt.Value = “Allan” Then

Sheets("Allan").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to AP Sheet _
if Meeting Attendee drop down menu equals AP
If cboMtgAtt.Value = “Allan” Then

Sheets("AP").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to CJ Sheet _
if Meeting Attendee drop down menu equals CJ
If cboMtgAtt.Value = “CJ” Then

Sheets("CJ").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to DB Sheet _
if Meeting Attendee drop down menu equals DB
If cboMtgAtt.Value = “DB” Then

Sheets("DB").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to DR Sheet _
if Meeting Attendee drop down menu equals DR
If cboMtgAtt.Value = “DR” Then

Sheets("DR").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to E Sheet _
if Meeting Attendee drop down menu equals E
If cboMtgAtt.Value = “E” Then

Sheets("E").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to EH Sheet _
if Meeting Attendee drop down menu equals EH
If cboMtgAtt.Value = “EH” Then

Sheets("EH").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to Eug Sheet _
if Meeting Attendee drop down menu equals Eug
If cboMtgAtt.Value = “Eug” Then

Sheets("Eug").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to GB Sheet _
if Meeting Attendee drop down menu equals GB
If cboMtgAtt.Value = “GB” Then

Sheets("GB").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to GG Sheet _
if Meeting Attendee drop down menu equals GG
If cboMtgAtt.Value = “GG” Then

Sheets("GG").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to JG Sheet _
if Meeting Attendee drop down menu equals JG
If cboMtgAtt.Value = “JG” Then

Sheets("JG").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to KK Sheet _
if Meeting Attendee drop down menu equals KK
If cboMtgAtt.Value = “KK” Then

Sheets("KK").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to KT Sheet _
if Meeting Attendee drop down menu equals KT
If cboMtgAtt.Value = “KT” Then

Sheets("KT").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to LV Sheet _
if Meeting Attendee drop down menu equals LV
If cboMtgAtt.Value = “LV” Then

Sheets("LV").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to MG Sheet _
if Meeting Attendee drop down menu equals MG
If cboMtgAtt.Value = “MG” Then

Sheets("MG").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to PB Sheet _
if Meeting Attendee drop down menu equals PB
If cboMtgAtt.Value = “PB” Then

Sheets("PB").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to RSSheet _
if Meeting Attendee drop down menu equals RS
If cboMtgAtt.Value = “RS” Then

Sheets("RS").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to RR Sheet _
if Meeting Attendee drop down menu equals RR
If cboMtgAtt.Value = “RR” Then

Sheets("RR").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to RW Sheet _
if Meeting Attendee drop down menu equals RW
If cboMtgAtt.Value = “RW” Then

Sheets("RW").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

' add data to TT Sheet _
if Meeting Attendee drop down menu equals TT
If cboMtgAtt.Value = “TT” Then

Sheets("TT").Activate
Range("A1").Select
Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

' enter data from form

End If

'clear the data in form
Me.txtArea.Value = ""
Me.cboMtgAtt.Value = ""
Me.cboStat.Value = ""
Me.cboPrior.Value = ""
Me.txtItem.Value = ""
Me.txtAct.Value = ""
Me.txtTgtDate.Value = ""
Me.txtCompDate.Value = ""
Me.txtObjt.Value = ""
Me.txtProj.Value = ""
Me.txtArea.SetFocus

End Sub

**royUK** · 07-27-2010, 09:55 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**royUK** · 07-27-2010, 09:57 AM

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.

Help with Userform Code

LinkBack

Thread Tools

Display

Hybrid View

Help with Userform Code

Re: Help with Userform Code

Re: Help with Userform Code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions