Solved: Issues with printing to excel

[pico]

I have written a macro to print information into cells in excel. Recently i right clicked a cell and used the "clear contents" button. From then on i have been unable to write to that specific cell. How can i correct this problem?

[lucas]

Hi pico,
could you give a little more info please...what do you mean by print info into cells?

[pico]

When i try to write to the cell from a macro. It will automatically print to the excel file.

[lucas]

Your going to have to post your code and/or your workbook because if I use:

Sub Macro1()
Range("D6").Select
ActiveCell.FormulaR1C1 = "kkkkkk"
Range("D7").Select
End Sub

to insert text into a cell in excel, I can clear contents with impunity and re-run the macro repeatedly with no problems. You must be doing some formatting or something before, after, etc. that I cannot understand because I don't know what your doing exactly....

[lucas]

The first was with the recorder but this works just the same...no problems:

Sub Macro1()
Range("D6").Select
ActiveCell.Value = "kkkkkk"
Range("D7").Select
End Sub

[pico]

ActiveWorkbook.Sheets("JOB_SPEC_FORM").Activate

If ChkBillAdd = True Then
'ActiveCell.Offset(6, 6) = True
Sheet1.OLEObjects("CheckBox1").Object.Value = True

Else
Sheet1.OLEObjects("CheckBox1").Object.Value = False
'ActiveCell.Offset(6, 6) = False
End If

'Sheet1.Visible = xlSheetVisible
Range("B7").Select
ActiveCell.Value = TxtDate.Value
ActiveCell.Offset(0, 6) = TxtJobLoc.Value
ActiveCell.Offset(2, 0) = TxtJobNum.Value
ActiveCell.Offset(2, 6) = TxtJobName.Value

ActiveCell.Offset(8, 0) = TxtCompName1.Value

ActiveCell.Offset(10, 0) = TxtContName1.Value

ActiveCell.Offset(12, 0) = Txt1Add1.Value
ActiveCell.Offset(13, 0) = Txt2Add1.Value
ActiveCell.Offset(14, 0) = Txt3Add1.Value
ActiveCell.Offset(15, 0) = Txt4Add1.Value
ActiveCell.Offset(16, 0) = Txt5Add1.Value
ActiveCell.Offset(18, 0) = TxtPhone1.Value

Range("H15").Select
ActiveCell.Value = TxtCompName2.Value
ActiveCell.Offset(2, 0) = TxtContName2.Value
ActiveCell.Offset(4, 0) = Txt1Add2.Value
ActiveCell.Offset(5, 0) = Txt2Add2.Value
ActiveCell.Offset(6, 0) = Txt3Add2.Value
ActiveCell.Offset(7, 0) = Txt4Add2.Value
ActiveCell.Offset(8, 0) = Txt5Add2.Value
ActiveCell.Offset(10, 0) = TxtPhone2.Value

UserForm1.Hide
Unload Me
UserForm2.Show

[pico]

I have attached the excel file where i print it to. The cell iam taking about are the Job Location and Job Name

[lucas]

I don't see anything here that could be causing the problem. Maybe someone else will look at it with us.

I wonder why at the bottom you hide userform1 and then you unload it?

You may have to give a little more pico.....so I can understand.

[lucas]

try the attachment again pico....

[lucas]

Click on the post Reply button on the left. not the quick reply, etc.
below where you post look for "Manage Attachments"

[pico]

I have attached the file ..book2.zip. I hide it because its a better way to unload the form for me. If i only unload i can see the form being unloaded.

[lucas]

Ok pico...I missed the attachment, sorry. I don't see any code in the workbook you attached and from the code you posted, I don see any sheet JOB_SPEC_FORM

I also cannot find a checkbox1 on sheet1
from your code:
ActiveWorkbook.Sheets("JOB_SPEC_FORM").Activate

If ChkBillAdd = True Then
'ActiveCell.Offset(6, 6) = True
Sheet1.OLEObjects("CheckBox1").Object.Value = True

Else Sheet1.OLEObjects("CheckBox1").Object.Value = False
'ActiveCell.Offset(6, 6) = False
End If
and what is ChkBillAdd
is that an object, range...?
please try to explain to me exactly what your trying to do. Did you get you code by using the recorder?

[lucas]

also, what form?

[pico]

Forget the checkbox . I think i kinda have it figured out. I have merged the cells for job location and job number. There seems to be a problem there

[lucas]

You should avoid merged cells at all costs......(my opinion)

[mdmackillop]

Hi Pico,
Rather than selecting cells, better to use a With statement.

'Sheet1.Visible = xlSheetVisible
With Range("B7")
.Value = TxtDate.Value
.Offset(0, 6) = TxtJobLoc.Value
.Offset(2, 0) = TxtJobNum.Value
.Offset(2, 6) = TxtJobName.Value
'etc.
End With