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

rizzo93

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

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
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
250
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
46,530
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
46,530
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
46,530
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,359
Messages
5,528,225
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top