next available row coding issue

**JAL_0110** · 05-21-2013, 03:16 AM

Hi All

I know there are a number of posts on this, but I can not see where this is going wrong. I have tried numerous bits of code but nothing seems to work. I know it must be simple (for someone who really knows what they are doing..... which is not me!).

I just want to save contents of userform in next available line in worksheet "UserFormDataEntry". Currently it just keeps saving to active worksheet.

Any help is GREATLY APPRECIATED

Thanks

Private Sub cmdSubmit_Click()

    
    If Input1.value = "" Or Input2.value = "" Or Input3 = "" Or Input4 = "" Then
    MsgBox "Please ensure the Date is completed in full and a Staff Name is selected."
    Exit Sub
    End If

    
    If Input1.value = "" Or Input2.value = "" Or Input3 = "" Or Input4 = "" Then
    MsgBox "Please ensure the Date is completed in full and a Staff Name is selected."
    Exit Sub
    End If
    
    
    If Input6.BackColor = &HC0C0FF Or Input8.BackColor = &HC0C0FF Or Input10.BackColor = &HC0C0FF Or Input14.BackColor = &HC0C0FF Or Input16.BackColor = &HC0C0FF Or Input18.BackColor = &HC0C0FF Or Input22.BackColor = &HC0C0FF Or Input24.BackColor = &HC0C0FF Or Input26.BackColor = &HC0C0FF Or Input30.BackColor = &HC0C0FF Or Input32.BackColor = &HC0C0FF Or Input34.BackColor = &HC0C0FF Or Input38.BackColor = &HC0C0FF Or Input40.BackColor = &HC0C0FF Or Input42.BackColor = &HC0C0FF Or Input46.BackColor = &HC0C0FF Or Input48.BackColor = &HC0C0FF Or Input50.BackColor = &HC0C0FF Or Input54.BackColor = &HC0C0FF Or Input56.BackColor = &HC0C0FF Or Input58.BackColor = &HC0C0FF Or Input62.BackColor = &HC0C0FF Or Input64.BackColor = &HC0C0FF Or Input66.BackColor = &HC0C0FF Or Input70.BackColor = &HC0C0FF Or Input72.BackColor = &HC0C0FF Or Input74.BackColor = &HC0C0FF Or Input78.BackColor = &HC0C0FF Or Input80.BackColor = &HC0C0FF Or Input82.BackColor = &HC0C0FF Then
    MsgBox "Please complete or re-type all the cells that are shaded pink and re-submit."
    Exit Sub
    Else
    End If


    
    Dim Row As Integer
    Row = 1

    Dim i As Integer
    For i = 1 To 156
    Worksheets("UserFormDataEntry").Range("A2").Offset(0, i - 1).value = _
        Me.Controls("Input" & i).value
    Next i


    Set Rng = Range("A2")
    Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, RngEnd.Offset(1, 0))

     With Rng

        .Offset(0, 0).value = Input1
        .Offset(0, 1).value = Input2
        .Offset(0, 2).value = Input3
        .Offset(0, 3).value = Input4
        .Offset(0, 4).value = Input5
        .Offset(0, 5).value = Input6
        .Offset(0, 6).value = Input7
        .Offset(0, 7).value = Input8
        .Offset(0, 8).value = Input9
        .Offset(0, 9).value = Input10
        .Offset(0, 10).value = Input11
        .Offset(0, 11).value = Input12
        .Offset(0, 12).value = Input13
        .Offset(0, 13).value = Input14
        .Offset(0, 14).value = Input15
        .Offset(0, 15).value = Input16
        .Offset(0, 16).value = Input17
        .Offset(0, 17).value = Input18
        .Offset(0, 18).value = Input19
        .Offset(0, 19).value = Input20
        .Offset(0, 20).value = Input21
        .Offset(0, 21).value = Input22
        .Offset(0, 22).value = Input23
        .Offset(0, 23).value = Input24
        .Offset(0, 24).value = Input25
        .Offset(0, 25).value = Input26
        .Offset(0, 26).value = Input27
        .Offset(0, 27).value = Input28
        .Offset(0, 28).value = Input29
        .Offset(0, 29).value = Input30
        .Offset(0, 30).value = Input31
        .Offset(0, 31).value = Input32
        .Offset(0, 32).value = Input33
        .Offset(0, 33).value = Input34
        .Offset(0, 34).value = Input35
        .Offset(0, 35).value = Input36
        .Offset(0, 36).value = Input37
        .Offset(0, 37).value = Input38
        .Offset(0, 38).value = Input39
        .Offset(0, 39).value = Input40
        .Offset(0, 40).value = Input41
        .Offset(0, 41).value = Input42
        .Offset(0, 42).value = Input43
        .Offset(0, 43).value = Input44
        .Offset(0, 44).value = Input45
        .Offset(0, 45).value = Input46
        .Offset(0, 46).value = Input47
        .Offset(0, 47).value = Input48
        .Offset(0, 48).value = Input49
        .Offset(0, 49).value = Input50
        .Offset(0, 50).value = Input51
        .Offset(0, 51).value = Input52
        .Offset(0, 52).value = Input53
        .Offset(0, 53).value = Input54
        .Offset(0, 54).value = Input55
        .Offset(0, 55).value = Input56
        .Offset(0, 56).value = Input57
        .Offset(0, 57).value = Input58
        .Offset(0, 58).value = Input59
        .Offset(0, 59).value = Input60
        .Offset(0, 60).value = Input61
        .Offset(0, 61).value = Input62
        .Offset(0, 62).value = Input63
        .Offset(0, 63).value = Input64
        .Offset(0, 64).value = Input65
        .Offset(0, 65).value = Input66
        .Offset(0, 66).value = Input67
        .Offset(0, 67).value = Input68
        .Offset(0, 68).value = Input69
        .Offset(0, 69).value = Input70
        .Offset(0, 70).value = Input71
        .Offset(0, 71).value = Input72
        .Offset(0, 72).value = Input73
        .Offset(0, 73).value = Input74
        .Offset(0, 74).value = Input75
        .Offset(0, 75).value = Input76
        .Offset(0, 76).value = Input77
        .Offset(0, 77).value = Input78
        .Offset(0, 78).value = Input79
        .Offset(0, 79).value = Input80
        .Offset(0, 80).value = Input81
        .Offset(0, 81).value = Input82
        .Offset(0, 82).value = Input83
        .Offset(0, 83).value = Input84
        .Offset(0, 84).value = Input85
        .Offset(0, 85).value = Input86
        .Offset(0, 86).value = Input87
        .Offset(0, 87).value = Input88
        .Offset(0, 88).value = Input89
        .Offset(0, 89).value = Input90
        .Offset(0, 90).value = Input91
        .Offset(0, 91).value = Input92
        .Offset(0, 92).value = Input93
        .Offset(0, 93).value = Input94
        .Offset(0, 94).value = Input95
        .Offset(0, 95).value = Input96
        .Offset(0, 96).value = Input97
        .Offset(0, 97).value = Input98
        .Offset(0, 98).value = Input99
        .Offset(0, 99).value = Input100
        .Offset(0, 100).value = Input101
        .Offset(0, 101).value = Input102
        .Offset(0, 102).value = Input103
        .Offset(0, 103).value = Input104
        .Offset(0, 104).value = Input105
        .Offset(0, 105).value = Input106
        .Offset(0, 106).value = Input107
        .Offset(0, 107).value = Input108
        .Offset(0, 108).value = Input109
        .Offset(0, 109).value = Input110
        .Offset(0, 110).value = Input111
        .Offset(0, 111).value = Input112
        .Offset(0, 112).value = Input113
        .Offset(0, 113).value = Input114
        .Offset(0, 114).value = Input115
        .Offset(0, 115).value = Input116
        .Offset(0, 116).value = Input117
        .Offset(0, 117).value = Input118
        .Offset(0, 118).value = Input119
        .Offset(0, 119).value = Input120
        .Offset(0, 120).value = Input121
        .Offset(0, 121).value = Input122
        .Offset(0, 122).value = Input123
        .Offset(0, 123).value = Input124
        .Offset(0, 124).value = Input125
        .Offset(0, 125).value = Input126
        .Offset(0, 126).value = Input127
        .Offset(0, 127).value = Input128
        .Offset(0, 128).value = Input129
        .Offset(0, 129).value = Input130
        .Offset(0, 130).value = Input131
        .Offset(0, 131).value = Input132
        .Offset(0, 132).value = Input133
        .Offset(0, 133).value = Input134
        .Offset(0, 134).value = Input135
        .Offset(0, 135).value = Input136
        .Offset(0, 136).value = Input137
        .Offset(0, 137).value = Input138
        .Offset(0, 138).value = Input139
        .Offset(0, 139).value = Input140
        .Offset(0, 140).value = Input141
        .Offset(0, 141).value = Input142
        .Offset(0, 142).value = Input143
        .Offset(0, 143).value = Input144
        .Offset(0, 144).value = Input145
        .Offset(0, 145).value = Input146
        .Offset(0, 146).value = Input147
        .Offset(0, 147).value = Input148
        .Offset(0, 148).value = Input149
        .Offset(0, 149).value = Input150
        .Offset(0, 150).value = Input151
        .Offset(0, 151).value = Input152
        .Offset(0, 152).value = Input153
        .Offset(0, 153).value = Input154
        .Offset(0, 154).value = Input155
        .Offset(0, 155).value = Input156
    End With




     For Each ctl In Me.Controls
       Select Case TypeName(ctl)
        Case Is = "TextBox", "ComboBox"
          ctl.value = ""
       End Select
    Next ctl
    
ActiveWorkbook.Save
End Sub

**arlu1201** · 05-21-2013, 03:47 AM

Can you also attach the sheet you are using?

What is Input1, Input2, etc? The names of the text boxes?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**JAL_0110** · 05-21-2013, 04:00 AM

vsa productivity userform.xlsmHi

Thanks for the reply.

Input1 to Input4 = combo boxes
Input5 to Input156 = Text boxes

If you are looking at the Private Sub cmdSubmit_Click() code can you please also look at the following code and see if you pick up anthing wrong

    Dim input0500check As Long
    input0500check = Input5.value + Input7.value + Input9.value + Input11.value + Input12.value
    
    If input0500check > 60 Then
    MsgBox "Minutes entered from 0500-0600 cannot exceed 60. Please revise"
    Exit Sub
    End If

It runs correctly, but once i change the text boxes to be <61, and press 'print and submit' again it still comes up with the error message box and will not submit. For the rest i think it works ok (other than saving to the wrong worksheet).

Thanks so much again for the help.

**Norie** · 05-21-2013, 04:17 AM

You have no worksheet reference here, so Rng will be referring to A2 on the active sheet.

    Set Rng = Range("A2")

Try adding a reference to the appropriate sheet.

**JAL_0110** · 05-21-2013, 04:48 AM

Hi Norie

I set

 
Set Rng = Worksheets("UserFormDataEntry").Range("A2")

It worked!! Something so simple almost made me go crazy

Thank you so much

If anyone can help with the other query relating to

 Dim input0500check As Long
    input0500check = Input5.value + Input7.value + Input9.value + Input11.value + Input12.value
    
    If input0500check > 60 Then
    MsgBox "Minutes entered from 0500-0600 cannot exceed 60. Please revise"
    Exit Sub
    End If

(as per above thread)
it would be greatly appreciated.

**Norie** · 05-21-2013, 05:36 AM

Textboxes contain text values, so to add there values arithmetically you need to convert the text to numbers.

There are various conversion functions you can use, eg CLng, CInt, Val etc

In this case I think CLng might be what you want, that will convert to Long which is the same data type as the variable you want to store the result in.

By the way, you could considerably shorten the first code you posted using loops and the Controls collection of the userform.

**JAL_0110** · 05-21-2013, 05:47 AM

Hi Norie

Thanks for the reply.

I dont doubt that I make the code much harder than it needs to be. I only started this a few weeks ago and I am really still just learning. I hope I get to that stage one day.