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

rizzo93

Active Member
Joined
Jan 22, 2015
Messages
272
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.
Code:
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:

Code:
Worksheets("Project Rep Data").Range(r, 41).Value = "hello"
What am I missing please?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,717
Office Version
  1. 365
Platform
  1. Windows
If this doesnt work:

Code:
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

Active Member
Joined
Jan 22, 2015
Messages
272
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you add this line
Code:
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It should be Cells(r,41) not range
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,297
Messages
5,836,481
Members
430,434
Latest member
whatabout

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top