VBA text disappears when the function that fills the text exits

Question

I'm new with VBA. I'm trying to write a function which fills some constant cells with constant text. This is my code:

Public Function PrepareSpreadSheet()
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "Sun"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "Mon"
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "Tue"
    Range("J5").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("B5:J5").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Function

I'm calling this function from my sub:

Sub ReadTxtFile()
   PrepareSpreadSheet
End Sub

When I run this nothing happens. When debugging, I see that the text is entered to the specified cells, but then disappears when the function exits.

Can you please explain how to fix this?

Thanks, Li

The function works as expected. It could be improved (see Sid's answer), but what I think is happening is that you have turned `Application.ScreenUpdating = false` off at some point and when the function executes the screen hasn't been updated. Screen through your code and look for `Application.ScreenUpdating = False` or simply add `Application.ScreenUpdating = True` after the `PrepareSpreadSheet` call inside the `ReadTxtFile` sub — , Oct 21 '13 at 10:36
Also, check if you have a `Cells.ClearContents` anywhere in your code. — , Oct 21 '13 at 10:40
@mehow: I have added your suggestion in point 4 in my answer. — Siddharth Rout, Oct 21 '13 at 10:41
@SiddharthRout but you didn't add mine. Is this is not something to be confirmed/checked? (even before anything else)? — varocarbas, Oct 21 '13 at 10:42
@varocarbas: Thats because I am not sure what you are trying to say... :) — Siddharth Rout, Oct 21 '13 at 10:43
@SiddharthRout you are welcome (the reason why I removed the upvote is because your answer does not contain a solution for the problem, which most likely is what I am proposing; also, to be honest, I have found a bit unpolite not having answered my help, but answering mehow one; politeness/corretness is one of the parameters I bring into account to upvote an answer :)).... — varocarbas, Oct 21 '13 at 10:45
@SiddharthRout what I said was that if you add this method to VBA and don't associate it to any relevant event (for example: the click of a button or the one being triggered when the spreadsheet is started) it will not be triggered. BUT, if you execute this code in debug mode it would be called anyway -> behaviour the OP is complaining about. — varocarbas, Oct 21 '13 at 10:47
@varocarbas: I was planning to answer your comment once I had finished updating my post and finished acknowleging mehow. If you noticed, the moment you commented on my post, I upvoted your comment to acknowledge that I had read your comment. Sorry, if I came across as being impolite as that was never the intention and I guess I have never been impolite to anyone on SO :) — Siddharth Rout, Oct 21 '13 at 10:48
How do you "run" your code? In other words, what do you do (or think you do) to call the sub? Could you put a `MsgBox "calling the sub"` in the sub to confirm it is actually running? — Floris, Oct 21 '13 at 10:48
@SiddharthRout Sorry for the misunderstanding. There you got your marked answer which compensates you for my change on mind. In any case, I continue thinking that the only situation explaining what the OP was complaining about (working on debug but not when being "run normally") is what I referred: not being linked to a valid event. But well... I would love to hear from the OP the actual reason to clear this issue out. — varocarbas, Oct 21 '13 at 10:51
@varocarbas: `...which compensates you for my change on mind...` I don't answer questions for points or acceptance or upvotes :) These points can't buy me Harley Davidson ;) — Siddharth Rout, Oct 21 '13 at 10:52
Thanks @mehow and you all, as the template was copied from the tutorial I did have Cells.ClearContents in my code. I'll edit the question so that it will include this code line. I accepted siddharthRout answer as it was really helpful as well. — user429400, Oct 21 '13 at 10:55
@SiddharthRout Then if you don't care about points, there is no problem... there was a small misunderstanding which I am happy that we have clarified but it has been a good excuse to make things fairer: 1+mark is enough for this answer. PS: you might change some points with a SO user having a Harley and wanting points... sorry cannot help: I am not interested in points neither own a Harley :) — varocarbas, Oct 21 '13 at 10:56
@user429400: where was the code `Cells.ClearContents` ? In a Sub or in the `worksheet_Change` event? — Siddharth Rout, Oct 21 '13 at 10:59

score 2 · Accepted Answer · edited May 23 '17 at 12:05

Why a Function and not a Sub? A function is used to Return something. Also Please avoid the use of .Select.

INTERESTING READ

This is what you are trying?

Sub ReadTxtFile()
   PrepareSpreadSheet
End Sub

Public Sub PrepareSpreadSheet()
    Dim ws As Worksheet

    '~~> Change this to the relevant worksheet 
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        .Range("B5").Value = "Sun"
        .Range("C5").Value = "Mon"
        .Range("D5").Value = "Tue"
        .Range("J5").Value = "Total"

        With .Range("B5:J5")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .ReadingOrder = xlContext
        End With
    End With
End Sub

If the above still doesn't work, then

Check if there is any code in the worksheet_Change event which might be clearing of the cells?
Check if the font color of that cell is not white?
Check if you are checking the correct worksheet. Maybe the data is in some other worksheet?
courtesy @Mehow: Setting the Application.ScreenUpdating = false can also not show the data. If you are setting the event to False then remember to set it back to True

All the corrections are fine (OP's code had quite a few weak points), but I don't think that all this can fix the referred problem. What the OP is claiming (working on debug, but not when executing the spreadsheet) sounds more like that this method is not linked to any relevant event (e.g., click of a button). — varocarbas, Oct 21 '13 at 10:34

VBA text disappears when the function that fills the text exits

1 Answers1