Posts by ojchase

    Re: Unexpected Circular References

    That seems to have taken care of the problem. I had a gut feeling it had something to do with that line but had no idea what to do with it. Thanks!

    I suppose it is an unconventional way to run the file. Can you tell what I'm trying to do and if so a better way?

    EDIT: I think we both responded at the same time...this was meant in response to your previous post. That new code does look like a more effecient way to do it, but I'm actually rather proud of myself the old way. Let's just say that modified in Word it took me 10 minutes to write and saved me a lot of time on my AP homework. I need to get to bed but I might try the code the new way tomorrow. In the mean time thanks a lot for a solution!

    Re: Unexpected Circular References

    I had originally made this code for use in Word, but tweaked it for it to work in Excel. It basically goes and activates the Bookmark corresponding to the number. So if you type in 1 in goes to the bookmark "one" which is attached to A1 which says "1". Wow that sounded weird. Perhaps there's a better way. I know you often don't need to select the cells, but I figured I did need to in order to use xldown in the next step.

    EDIT: I think I may have used the Macro Recorder originally to help me with that step so that's why it looks so funny. Is that called syntax maybe? It also works as:

    Application.Goto (Response)

    I have no idea how to even explain this problem so I have attached the workbook. Basically a macro adding a book to A and then another removing the book doesn't really remove the book when in column A. Instead it makes a circular reference. I have no idea why. That probably made no sense but at least I tried.

    To reproduce error:
    1. Open workbook
    2. Run Sub Addbook in Module 1
    3. Enter a name
    4. In the second dialog box type the number "1"
    5. Run Sub Deletebook in Module 1
    6. Type the same name
    7. Observe the Circular reference problem that seems to have crept into Cells A3:E3
    8. If you want, try the same thing in another column. I don't think it occurs elsewhere.

    I have no idea why this is happening or how to better explain it without the file. If you have any great suggestions (ie solutions) or questions please let me know. Thanks!

    Re: Insert Vba Code With Vba

    That page isn't going to work for me. If nothing else it sets off the Symantec virus scan Bloodhound thing, which I can't deal with. Need to do it another way. What is the method to programatically insert code into a module (or more specifically here - the code attached to a worksheet)? I have been experimenting with VBE, VBProject, VBIDE, vbcomponents, codemodule, all kinds of things that look like they might be related but am not having any luck. It's all in the same workbook. There's got to be a method to add code automatically, but I'm not finding it.

    EDIT: On second thought, I just had a breakthrough. I have the line:

    Application.VBE.ActiveVBProject.VBComponents(NewName).CodeModule.AddFromFile ("E:\code.txt")

    in the document now, and it works if I change NewName to another name, such as "Sheet1". However, this is part of a larger code and it needs to be variable just as it is. Is there a way to change a worksheet from, for example, "Sheet1(Home)" to "Home"? Because if I could do that then I could just make the line before the one posted change the name to whatever NewName is and then the code would work! The second question I have is if there is a way to do this exact same thing without specifying "E:\code.txt". In that file are two sub routines, but I'd like to be able to distribute this project without that file if possible.... Thanks!

    I have a code that needs to be placed on any new worksheets created. All new worksheets are made using VBA code already so they just need to be told to add the code into each new sheet. How do I do this?

    Re: Insert Vba Checkbox

    Quote from lenze

    Thank you so much. I modified this a bit and it worked for what I needed. I'm using the Wingdings font instead of marlett and my code is:

    Thanks so much!

    Re: Insert Vba Checkbox

    That looks very interesting and complicated. I will try it during my class tomorrow, but for now I was wondering if the same concept should work for adding three different states. Unchecked, checked, and "checked without a checkbox". Do you think that would work too?

    I need a sub routine to insert a checkbox into a cell. If possible it needs to be automatically centered in the cell into which it is inserted, and hopefully stay centered even if the sizes of the column change. What's the easiest way to do this?

    Re: Repeating Subs

    Quote from Richie(UK)


    How about something like this?

    I think that did the trick. Thanks so much!

    How can I have a sub repeat itself? I have a code and I want it to ask the user at the end if they would like to do it again. If so the whole sub needs to be repeated. I'm sure this has something to do with looping, but I don't know how to manage this. Thanks in advance.

    Re: Vba To Insert Command Button

    That works for adding a button, which I see is attached to the AddLimitsToChart macro you must have created. That is more than I knew how to do; thanks. However, what I really need is not only for the button to be created but for it to be created as an identical copy of the other button on another sheet. Like a clone. It would be nice if it automatically updated itself each time the spreadsheet turned on as well. It can't just be a link; it needs to be a duplicate link. Does that make more sense?

    Hey. I am using a button on a custom-made form to create a new worksheet. Clicking the button will:

    1. Ask for a name
    2. Make a worksheet using that name
    3. Give worksheet a random color
    4. Insert Name into A1
    5. Bold, and change name to size 24 in A1
    6. Insert a command button into the new worksheet.

    Step #6 is the ONLY one that doesn't work, mostly because I don't know how to do it. Another worksheet has a command button on it already that is attached to a totally different macro. I would like an exact duplicate of that command button on every new page added with this user form. (It can't be a copy and paste code in case I should update the other button in the future.) How can I use VBA code to automatically create a working command button for step 6?

    Re: Random Pattern Is Repeating

    Right in here?

    Dim Random As Integer
        Dim item As Range
        For Each item In ActiveWorkbook.Worksheets
        Random = Rnd(1) * 56
            If Random = 0 Then
            Random = Rnd(1) * 56

    Re: Random Pattern Is Repeating

    I'm new at this. What do you mean by codename?

    I copied and pasted the code into the editor. Upon running it wanted me to define the variable scrappaper, so I changed the line

    If Scrappaper.[F2].Value = Date Then Exit Sub


    If Sheets("Scrappaper").Range("F2").Value = Date Then Exit Sub

    Then the code works. However, now when the document opens it has a different random pattern at the beginning than it did before, but the pattern still repeats with each opening.

    Hello. I made a code that automatically checks the date and then compares it to the date of the last save. If it is a new date then the colors of the tabs will randomize. It can also be done manually via a button. The problem is that I have now realized that the colors are always have the same pattern. Every day the first color is an orange color, no matter how many times I randomized yesterday...and so on. Is there a way to make the randomness not have a repeating pattern? My code:

    Also, off topic I know, I would like to be able to make the cell F2 locked so that the only way it can be changed is by this sub. The end user can't modify the cell but the VBA can...and I don't want the whole worksheet locked. Just the cell. Any ideas there? (If you can't answer because it's one question per thread just let me know)

    Re: Cause For Type Mismatch Error

    Quote from shg

    OK, this may do what you want:

    If it doesn't, please post an example of your data and (on a different sheet) your expected results.

    Thank you! That worked exactly as I wanted (except that it took some data I had already fixed and re-fixed it (ie Due Due 4 Times Times)...not your fault). I also added the line:

    Selection.HorizontalAlignment = xlCenter

    to simulate the "Merge and Center" option. Now I get to figure out what that code means......someday! Thank you very much.

    Re: Cause For Type Mismatch Error

    I want to merge horizontally. I have a list of numbers in column B. In column C (this is new), I have the word "yes" occasionally. No row has both a number in column B and a "yes" in column C. What I would like (and I acknowledge that I am very new to this, haven't even learned looping yet jindon) is for a macro to go through column B. If it finds a value or a number in any cells in column B, then it will take that cell and the cells in the same row over to column H (such as B13:H13) and merge them, then change the text to "Due # Times".

    So I tried to write a macro for this and if I have it look at an individual cell in column B (like B13) it works and merges B13:H13. However, for whatever reason (most explanations have gone over my head thus far), this no longer works if I tell it to do more than one cell in column B. It would currently be quicker for me to just go through and update manually, but I'd like it to work.

    Macro searches for any values in column B.
    If a value is found, macro changes the cell to say "Due "value" times" and then merges the cell in column B with the blank cells across in C,D,E,F,G,and H. I'd be glad to attach the file if needed. Thanks.

    Re: Cause For Type Mismatch Error

    Quote from the Okk

    You can not compare array of values (B13,B14,B15...B37) and only one value (4). It's impossible. Does that make more sense? :)

    Yes, but what do I do about it? I want it to check Column B for all cells with a value. Then it will take whatever values it finds, and merge the cell with c,d,e,f,g,and h, and change the text to "Due [number] times" I understand to some degree what the problem is, but I don't quite understand why since array formulas (non vba) work like that.

    Jindon, I did not try your code. The reason is that I need the IF statement to check more than one cell. It needs to check the whole column and do what already works for an individual cell multiple times.

    Re: Cause For Type Mismatch Error

    Quote from jindon

    Naturally, when you merge B11:B13, No more Rasnge("B13") to be referred to..

    Dim temp
    If Worksheets("Daily").Range("B13").VAlue = 4 Then
    temp = Range("b13").Value
    Range("B11").VAlue = "Due " & temp & " times"
    End If

    I'm sorry but I think I must have phrased things wrong. The code using B13 only works exactly the way it should. I would like the same procedure to be done on any number in a larger range (B13:B37). Does that make more sense?