Why can't I get this string sent to a cell?

[rizzo93]

I'm launching a user form from a worksheet. I then concatenate the values from that form to make a string and sending its value to a cell on another worksheet.

Private Sub SaveButton_Click()
Dim resources As String
Dim r As Integer
r = ActiveCell.Row
    If ProjectSponsorBox.Value <> "ERROR" And BusinessBox.Value <> "ERROR" And SolutionBox.Value <> "ERROR" And _
    OperationalBox.Value <> "ERROR" And FL1ExpBox.Value <> "ERROR" And FL2ExpBox.Value <> "ERROR" And _
    FL3ExpBox.Value <> "ERROR" And FL4ExpBox.Value <> "ERROR" And FL1Box.Value <> "ERROR" And FL2Box.Value <> "ERROR" And _
    FL3Box.Value <> "ERROR" And FL4Box.Value <> "ERROR" Then
    
        resources = "1. Business, " + BusinessBox.Text + " " _
            + "2. Solution, " + SolutionBox.Text + " " _
            + "3. Operational, " + OperationalBox.Text + " " _
            + "4a. " + FL1ExpBox.Text + ", " + FL1Box.Text + " " _
            + "4b. " + FL2ExpBox.Text + ", " + FL2Box.Text + " " _
            + "4c. " + FL3ExpBox.Text + ", " + FL3Box.Text + " " _
            + "4d. " + FL4ExpBox.Text + ", " + FL4Box.Text
[COLOR=#ff0000]        Worksheets("Project Rep Data").Range(r, 41).Value = resources[/COLOR]
    Else
        MsgBox "You cannot commit changes when 'ERROR' is a value."
    End If

Call ContentBox_Change
End Sub

Every time it hits the red line above I get this error: Application defined or object defined error.

I've tried Long and Variant for the variable "resources", but it still doesn't work. I've even tried just assigning a value straight to that cell, but it still won't take it:

Worksheets("Project Rep Data").Range(r, 41).Value = "hello"

What am I missing please?

 

[steve the fish]

Try using & not +

 

[steve the fish]

If this doesnt work:

Worksheets("Project Rep Data").Range(r, 41).Value = "hello"

then id assume that the cell/ sheet is protected presuming r is an integer and the sheet exists.

 

[rizzo93]

Thanks, Steve.

I tried & and nothing changed.


Yes, you are correct: r is an integer and the sheet does exist.


I feel like the answer is staring right at me, but this is just too weird for it not to work.

 

[Fluff]

If you add this line

If ProjectSponsorBox.Value <> "ERROR" And BusinessBox.Value <> "ERROR" And SolutionBox.Value <> "ERROR" And _
    OperationalBox.Value <> "ERROR" And FL1ExpBox.Value <> "ERROR" And FL2ExpBox.Value <> "ERROR" And _
    FL3ExpBox.Value <> "ERROR" And FL4ExpBox.Value <> "ERROR" And FL1Box.Value <> "ERROR" And FL2Box.Value <> "ERROR" And _
    FL3Box.Value <> "ERROR" And FL4Box.Value <> "ERROR" Then
[COLOR=#ff0000]        MsgBox "Hi"[/COLOR]
        resources = "1. Business, " + BusinessBox.Text + " " _

Does the message box appear?

 

[rizzo93]

Hi Fluff,

Yes, it does show up.

 

[Fluff]

It should be Cells(r,41) not range

 

[rizzo93]

OMG!!

Time to get new glasses.


Thanks for the eagle eyes, Fluff.

 

[Fluff]

You're welcome & thanks for the feedback