Posts by Jong

    What you could do as an extension to what you have already done, is to dynamically generate charts to reflect your data.

    For example, you could output a chart of your expenses Vs Income, and see how broke your getting.

    Also, you could chart your school grades, and see if there is a trend with the time of year or something.

    Finally, a chart for your jogging could reflect your progress, such as distance per month or whatever.

    The possibilities are endless.

    Figured it out

    Well, after trying many things, I have finally come up with a solution to this problem. If anyone else is interested, or if anyone searches for this topic in the future, here is what i did to solve my problem.

    The following code was inserted in the "ThisWorkbook" Excel object:

    Basically, what happens is. when you select a new worksheet, it will store the number of comments found in that worksheet.

    Once a new range is selected, or a new worksheet is selected, it will check the number of comments in the worksheet again. If this number is not the same, then a comment was changed.

    It probably isn't the most efficient way to do it, although it seems to be the only way if you ask me :)

    If anyone has any other ways to do this let me know.

    Is it possible to execute a function IF a comment was inserted on a particular worksheet?

    My application dynamically creates new worksheets, and data is constantly changed. I need to be able to call a function regardless of the worksheet after a user enters a cell comment.


    My initial idea was to try and figure out a way to programatically add code to the worksheet object as it is created.. Although I wasnt able to figure out how to do so. (Worksheet object, as in the code that handles the worksheet events)

    If anyone can tell me how to "programatically" add code to the worksheet object, that would also help.


    I'm not sure if excel is able to read directly from a PDF, however, it is possible to export the PDF file into a TEXT file.

    With the text file, you can import the data directly into excel.

    To import a TXT file the easy way, you can click

    then open your TXT file.


    YAY! Thanks for all the help guys...

    Here is my final code for those who are interested:

    Range("B7:B" & LowerRow).Select
    Selection.PasteSpecial Paste:=xlPasteComments, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    For some reason, this didn't work

    ws.Range("B7:B" & LowerRow).PasteSpecial Paste:=xlPasteComments, _
       Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    It seems I cannot programmatically Add a comment into a cell if the worksheet doesnt contain a comment.

    If the worksheet contains a comment, i can programatically add more. although if there are not comments already inserted, it crashed saying i'm not allowed.

    I Hope someone can help me on this one.

    New Problem

    When I try to paste a comment into a Range that doesnt contain a comment, it crashes my application.

    If i manually create a comment in that cell, and try again, it works.

    It seems that the code will not allow me to paste a comment into a cell that doesnt already have a comment in it.

    Anyoine have any suggestions?



    You can make a variable global by using the 'Public' keyword.

    Just put "Public myVar() As TYPE" somewhere outside of any functions or subs. You can then use this variable anywhere in your code.

    what i do

    Here is what I do for significant digits. the code is setup for your question.


    We always want numbers less than 1 to show 3 decimal places
    Numbers between 1 and less than 10 should show 2 decimal places.
    Numbers between 10 and less than 100 should show 1 decimal place.

    if cell.value < 1 then
         cell.NumberFormat = "0.000"
    elseif cell.value < 10 then
         cell.NumberFormat = "0.00"
    elseif cell.value < 100 then
         cell.NumberFormat = "0.0"

    Okay, here's my problem.

    I am sorting 'Column A' by Date, and the related row values are not swapped as the sort progresses.

    Here is my code:

    If Worksheets("DATA").Range("A1") = "Inorganic Sample" Then
                Selection.Sort Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
                    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

    What I really want to do is sort the entire rows as 'Column A' is sorted. At the moment, all that is being sorted is ColumnA. I need to get the row associated with each item in the column to move with it.

    I hope this makes sense.

    I am trying to figure out how to Copy a cell comment into another cell.


    Something along those lines, but I have no clue how to retrieve the comment, and copy it over to the new cell.

    Hopefully someone can help out.



    You could also add the macro in the menu bar up on top of the excel GUI.. Then name the button "Click me on monday" or something like that. That way it will be visible to all users that are using the notebook.

    Currently, I am doing the following to launch Internet Explorer from my VBA code:

    IEpath = "C:\program files\internet explorer\iexplore.exe"
    Shell IEpath & " " & filename, vbNormalFocus

    My concern is that, when another user tries to run my macro, it might crash since the path name might be different.

    My question is as follows...
    Is there any way to only specify the IExplore.exe such as in the RUN command, and make it open without the full path name?
    I'm basically trying to find a more generic/global way of doing this.



    It needs to be done strictly in code (VBA).

    Iv'e been working on this as I was waiting for a response:

    Where CommentString = "Jong: blahblahblah blah blah blah"

    Can anyone tell me how they do the following:

    - Take a string, for example: "Jong: Hello I'm Jong"
    - Extract the "Jong" from the string, resulting in 2 separate strings

    String1 = "Jong"
    String2 = "Hello I'm Jong"

    At the moment, I am using the Split function.. I want to try to access each character one at a time, but it seems VB doesnt allow indexing strings.

    Iv'e managed to create a Char Array, and store the string this way, but this is too much of a hassle. If anyone else has suggestions on how to do the above, please let me hear your thoughts.