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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

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?
 
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,114
Messages
5,835,462
Members
430,358
Latest member
zzc1128

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