Glitch when copying values with vba

Sigma1061

New Member
Joined
Jun 20, 2005
Messages
2
Hello,

I normally can find an answer to any problems I have on my own, but this one just bothers me.

I am using a UserForm to compare two Worksheets with current and new data. The UserForm displays the value of cell A1 from WorkBook1 in a Label on the Form, and displays the value of cell A1 from WorkBook2 in another Label on the Form. The UserForm then allows me to keep the current value for the cell, replace it with the new value, or edit the value from either. It works great.

The problem:

A lot of the cells have line breaks in them (Alt+Enter). They show up fine in the Labels on the Form. When these cell values are replaced by the Label.Caption, the end of each line has some strange character added. One of those box characters for when there is no correct character. Like [].

Code:
'Sets the Label as the value of the new WorkBook cell
ChangesLabel.Caption = UpdateVersion.Sheets(1).Cells(x, y).Value
.
.
.
'A button press initiates this, which sets the current WorkBook cell to the value of the Label caption:
CurrentVersion.Sheets(1).Cells(StartRow, StartColumn).Value = ChangesLabel.Caption
'The code then checks the whole worksheet for other changes in cell values
So for a cell it might look like:
11/22/05[]
11/23/05[]
9/17/05[]
11/19/05 (the last line never has it)

Thoughts?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Sigma1061

New Member
Joined
Jun 20, 2005
Messages
2
Well...of course as soon as I posted that I immediately saw what I can do to fix it, which is to cut out the Label entirely and go from cell to cell when actually changing it.
Code:
 'Sets the Label as the value of the new WorkBook cell
ChangesLabel.Caption = UpdateVersion.Sheets(1).Cells(x, y).Value
.
.
.
'A button press initiates this, which sets the current WorkBook cell to the value of the Label caption:
CurrentVersion.Sheets(1).Cells(StartRow, StartColumn).Value = UpdateVersion.Sheets(1).Cells(StartRow, StartColumn).Value
'The code then checks the whole worksheet for other changes in cell values
Even though I seem to have fixed it, can anyone say why setting the value of a cell to a multiline Label caption would insert such characters?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,016
Messages
5,569,628
Members
412,284
Latest member
Daibear
Top