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

Don't use a checkbox

http://vbaexpress.com/kb/getarticle.php?kb_id=879

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:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    checkboxval = ActiveCell.Value
    Select Case checkboxval
        Case ""
            ActiveCell.Value = "o"
        Case "o"
            ActiveCell.Value = "þ"
        Case "þ"
            ActiveCell.Value = "ü"
    End Select
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    ActiveCell.Value = ""
    Cancel = True
End Sub

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)

Hi,

How about something like this?

Code

Sub Test()
    Dim bExit As Boolean
    
    bExit = False
    Do
        'do your stuff here
        If MsgBox(prompt:="Do you wish to repeat?", Buttons:=vbYesNo) = vbNo Then
            bExit = True
        End If
    Loop Until bExit = True
    
End Sub

Display More

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

That worked. Thank you very much!

Re: Random Pattern Is Repeating

Right in here?

Dim Random As Integer
    Dim item As Range
    Randomize
    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

to

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:

Sub MMVII_4_9_b()
    Dim NewDate
    If Sheets("Scrappaper").Range("F2") = Date Then
        NewDate = 0
    Else
        NewDate = 1
        End If
        'Determines whether it is a new day or not
    Sheets("Scrappaper").Range("F2") = Date
    'Updates the date
    If NewDate = 0 Then
    'Does nothing if the date is the same
    Else
    Dim Random As Integer
    Dim item As Worksheet
    For Each item In ActiveWorkbook.Worksheets
    Random = Rnd(1) * 56
        If Random = 0 Then
        Random = Rnd(1) * 56
        Else
        End If
        'Error trapping...the number 0 breaks it
        'This will force the number 0 to be redrawn
    item.Tab.ColorIndex = Random
    'Changes tab to a random color
    Next item
    End If
End Sub

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:

Code

Sub MergeEm()
    Dim rCell As Range
    Dim sVal As String
    
    Application.DisplayAlerts = False
    For Each rCell In Intersect([B:B], ActiveSheet.UsedRange)
        rCell.Select
        If Selection.Row = rCell.Row Then
            sVal = rCell
            If sVal <> "" Then
                Selection.Resize(, 7).Merge
                Selection = "Due " & sVal & " times"
            End If
        End If
    Next
    Application.DisplayAlerts = True


End Sub

Display More

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

Quote from shg

OJ,

A few things:

o When you merge a range of cells, only the cell in the upper left of the merged range retains a testable value.

o In your posts below, you say at one point that you want to merge cells horizontally (b13:h13), and then later that you want to merge cells vertically (b11:b13). The first makes a little more sense to me that the second, but can you take us back to first principles on why you want to do either? I ask because,

o It is almost always bad practice to merge cells, and you've provided a good example of why. (Search for 'merged cells' in the forum for reinforcement.) So,

o May we help you find another way to get the results you want in a different way?

Kind regards,

Display More

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.

Summary:
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..

Code

Dim temp
If Worksheets("Daily").Range("B13").VAlue = 4 Then
temp = Range("b13").Value
Range("B11:B13").Merge
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?