Procedure too large

[purplehaz]

I have a spreadsheet that has a button that pops up a form. On the form you
enter in the row number and comments into text boxes. Upon clicking the ok
button the below code runs. It copies and pastes text from a text box in the
form to the row and cell designated. It works great, but the problem I have
is I have to do row 6 thru row 455. So I copy and paste the code and change
the row to seven, then copy and paste again and change to 8....etc... all
the way to row 455. When I run it I get a Procedure To Large compile error.
Says the Sub procudure must be smaller than 64k. I can only get it up to row
79 for it to work. Any code after that and I get the error. Any ideas on how
I can get this to work? Thanks.

Private Sub cmdOK_Click()
If TextBoxRow.Text < 6 Then
MsgBox "Bids and Comments must start on Row 6. Rows 1-5 are reserved for the
program.", vbOKOnly + vbCritical, "Error!"
End If
If TextBoxRow.Text = 6 Then
With ActiveSheet
If .Range("AO6").Text = "" Then
.Range("AO6").Formula = TextBoxComment1.Text
ElseIf .Range("AO6").Text <> "" Then
If .Range("AP6").Text = "" Then
.Range("AP6").Formula = TextBoxComment1.Text
ElseIf .Range("AP6").Text <> "" Then
If .Range("AQ6").Text = "" Then
.Range("AQ6").Formula = TextBoxComment1.Text
ElseIf .Range("AQ6").Text <> "" Then
If .Range("AR6").Text = "" Then
.Range("AR6").Formula = TextBoxComment1.Text
ElseIf .Range("AR6").Text <> "" Then
If .Range("AS6").Text = "" Then
.Range("AS6").Formula = TextBoxComment1.Text
ElseIf .Range("AS6").Text <> "" Then
MsgBox "At this time comments are limited to 5 fields! See
the system administrator if more comment fields are needed.", vbOKOnly +
vbCritical, "Error!"
End If
End If
End If
End If
End If
End With
End If
End Sub

 

[Dave Peterson]

It looks like you're picking up the row from the textbox (or was 6 just a
coincidence???).

If that's true, then maybe you can use that fact in your code:

Option Explicit
Private Sub cmdOK_Click()

Dim myRow As Long

myRow = CLng(TextBoxRow.Text)

If myRow < 6 Then
MsgBox "Bids and Comments must start on Row 6." & _
" Rows 1-5 are reserved for the program.", _
vbOKOnly + vbCritical, "Error!"
Else
With ActiveSheet
If .Range("AO" & myRow).Text = "" Then
.Range("AO" & myRow).Value = TextBoxComment1.Text
ElseIf .Range("AP" & myRow).Text = "" Then
.Range("AP" & myRow).Value = TextBoxComment1.Text
ElseIf .Range("AQ" & myRow).Text = "" Then
.Range("AQ" & myRow).Value = TextBoxComment1.Text
ElseIf .Range("AR" & myRow).Text = "" Then
.Range("AR" & myRow).Value = TextBoxComment1.Text
ElseIf .Range("AS" & myRow).Text = "" Then
.Range("AS" & myRow).Value = TextBoxComment1.Text
Else
MsgBox "At this time comments are limited to 5 fields!" & _
" See the system administrator" & _
" if more comment fields are needed.", _
vbOKOnly + vbCritical, "Error!"
End If
End With
End If
End Sub


And a note about your if/then/elseif's

You had code like:

If .Range("AO6").Text = "" Then
.Range("AO6").Formula = TextBoxComment1.Text
ElseIf .Range("AO6").Text <> "" Then

If AO6 = "" then do something, but then inside your elseif, you check to see if
AO6 is different than "".

The test (if AO6 = "") returns true or false. It's true if the cell looks
empty. It's false if there's something in it.

In this case, you already know the answer (since it's either empty or has
something in it).

So you don't have to check it again.

 

[purplehaz]

Thank you very much. I'm going to try this out. I'm new to vb and just
learning as I go. This helps alot.