Posts by jproffer

Re: Msg Box will not close

I removed the Unload command because I figured you wanted them to go back and enter their email address. But however it suits you, it suits me

Glad it worked out.

Re: Msg Box will not close

Looks like you were overthinking the problem.

Try this out and see if it does what you want it to do.

Private Sub IAccept_Click()
If TextBox3.Text = "" Then
    Msg = "You must enter an email address to continue"
    MsgBox Msg
        TextBox3.SetFocus
Else
     IAccept = True
End If
End Sub

Re: Pasting a column to the next blank column in a different sheet

Try this out:

Sub test()
Dim NextCol As Long
    If Sheets("Summary Sheet").Range("A1").Value = "" Then
        NextCol = 1
    Else
        NextCol = Sheets("Summary Sheet").Cells(1, Columns.Count).End(xlToLeft).Column + 1
    End If
        ActiveSheet.Range("J33:J52").Copy Sheets("Summary Sheet").Cells(1, NextCol)
End Sub

Re: Incrementing two For-Next loops at the same time

I'll add another one, then I'll leave this alone....

Don't misunderstand what we said about the link. If you are going to cross-post, it's acceptable, but usually not necessary. The vast majority of users of this forum also spend time at the others as well...sorry Dave, lol. But IF you are going to, we DO appreciate a link back to the other forum(s)....however, it would be nice if you would post your question HERE as well. If for no other reason that it shows us your willing to put a small amount of effort into helping yourself. Even a copy and paste would be ok...an original post (which might have more/different details than the other) would be even better.

Anyhow, if your answer on the other forum wasn't coming from mikerickson (or a handful of others), I'm sure it would have been answered here...even with the short post.

And please don't take all this as a reason to leave this forum...just don't drop links as "questions" and you'll be fine

Re: Incrementing two For-Next loops at the same time

Quote

Next to that, posting a link is not very polite.

Agreed!! Was trying to be nice (as much as possible), but I whole-heartedly agree.

Re: Incrementing two For-Next loops at the same time

I'm quite sure Mikerickson can help you. Give him some time

He's been around a long time (there and on other forums, maybe this one as well), and his solutions are always very well thought out and easy to understand.

Re: How to stop a new macro being recorded

If you password protect the VBA project, the recorder won't work.

Note: The VBA project can't be blank, or it won't take protection. Insert a module and type out any subroutine...

Sub test
MsgBox "Hi"
End Sub

or whatever, then it will allow protection.

Re: Use userform textbox to get user text input

Public variables don't hold their value forever. You could create a worksheet and hide it, then make a cell in that worksheet equal to the variable value before you unload the userform. Then on next run, grab the value from that cell back to the textbox.

Re: Add 1 if more than last digit changes, otherwise add 0

Not a problem. Glad to help :cheers:

Re: Add 1 if more than last digit changes, otherwise add 0

The top one (I used cell A2) will always be 1...then in B3, try this:

=IF((RIGHT(A3,LEN(A3)-2)*1)-(RIGHT(A2,LEN(A2)-2)*1)>9,B2+1,B2)

that assumes there are always 2 letters at the beginning and only numbers beyond that.

Re: add the sum of a group of cells but drop the two highest values

I haven't tested it, but wouldn't you have to array that k value?

=SUMIF(A1:A100,"<"&LARGE(A1:A100,{1,2}))

Just a question....I don't know for sure.

EDIT: DING...it just hit me......less than the second highest value.....no need to call out the highest value at all. Nevermind....carry on

Re: Row removed based on criteria

Try this one:

Sub Removetextrow()
     
    Dim WS As Worksheet
    Dim MyRange As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For Each WS In ActiveWorkbook.Worksheets
        With WS
            With WS.Cells
                Do
                    Set MyRange = .Find("Actual:", LookIn:=xlValues, LookAt:=xlWhole)
                    If MyRange Is Nothing Then Exit Do
                    MyRange.EntireRow.Delete
                Loop
                     
                End With
            End With
        Next WS
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationManual
    End Sub

Re: Hiding and unhiding sheets with VBA

wsCubeArray looks like it's not defined....try using:

Sheets("CubeArray").Visible = True
Sheets("CubeArray").Copy Before:= Sheets(1)
Sheets("CubeArray").Visible = xlHidden

assuming, wsCubeArray is supposed to mean that worksheet copying to another location.

Re: Hiding and unhiding sheets with VBA

Sheets("Sheet1").Visible = True

then your code, then:

Sheets("Sheet1").Visible = xlHidden
    'or
    Sheets("Sheet1").Visible = xlVeryHidden

Re: add the sum of a group of cells but drop the two highest values

=SUM(SMALL(A1:A6,{1,2,3,4}))

That will only work for 6 cells.

EDIT: Actually, that's not true. It will work for any number of cells, but it will only SUM the 4 smallest values.

Re: Row removed based on criteria

You're missing an End With after the other End With and before "Next WS"

Re: Total Instances of Same Value in Column A into Column B - Value ALWAYS Varies

Assuming the data you gave is in A1:A13, you could use this in B1:

=COUNTIF($A$1:$A$13,"=" & A1)

and drag it down.

Re: Tab order in excel

Back tab is shift+tab, normally, but I'm sure that won't work with a defined tab order.

IMO, tab order and back-tab order are related enough to continue this thread....but it's not my forum .....so I dunno...do whatever you feel like doing I guess.

Re: Tab order in excel

forum.ozgrid.com/index.php?attachment/45019/

Try that one. A few different references made it clear the upper ones...the lower ones still kicked it back up, but I unmerged those and formatted them to center across selection. When you start to type into them, it will look the same...in fact if you turn off gridlines, it will look exactly like it did.

Re: Tab order in excel

Ok.

It has something to do with the merged cells. I unmerged the cells that weren't working right, and it worked fine.