Posts by rory

Quick question - are you on version 2212 by any chance? I've seen quite a few reports recently of weird errors with workbooks with userforms on that build. There is apparently a fix in the works.

If you only need this range to be processed in a macro, why not just pass the dynamic range you already have and simply process every other row in the code (Pretty much as Carim showed near the beginning of this thread). Or do you need this named range for something else?

When you show a form, it is first loaded into memory and any code in its Initialize event is run (this is usually where the problem with a userform.show line actually is) then the form is displayed and any Activate code is run.

As a side note, it is generally better in my experience not to run code directly from the Workbook_Open event. If you use Application.Ontime to schedule any startup code (you can use Now as the schedule time), it gives Excel time to finish all its startup processes (particularly necessary when you open the workbook directly from explorer, thereby also loading excel).

It is rare indeed that that line would cause an error. It is more likely to be code in the Initialize or Activate event of that form.

No worries - we've all done it.

The first range reference needs to be absolute too:

=INDEX(Step3!$B$2:$B$18675,MATCH($G2,Step3!$D$2:$D$18675,0))

I did say "if there is something in brackets"...

If there might not be, then perhaps:

=IF(COUNT(FIND("(",A1)),SUBSTITUTE(SUBSTITUTE(A1,"0(","("),"0(","("),TRIM(SUBSTITUTE(SUBSTITUTE(A1&" ","0 "," "),"0 "," ")))

If there is something in brackets, then this should work:

=SUBSTITUTE(SUBSTITUTE(A1,"0(","("),"0(","(")

Try this:

Sub HideObjects()
Dim wv As WorksheetView
With Application
.ScreenUpdating = False
.DisplayFullScreen = True
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
For Each wv In ActiveWindow.SheetViews
wv.DisplayHeadings = False
wv.DisplayGridlines = False
Next wv
Application.ScreenUpdating = True
End Sub

As I said, either do it in sections:

Range("D10:d16,d20:d26").ClearContents
Range("L115:L121").ClearContents

or use Union:

Union(Range("D10:d16,d20:d26"),Range("L115:L121")).ClearContents

For the other part you can use conditional formatting to colour cells that are not empty.

Just in case, I'd suggest you split the code into two routines and use Application.Ontime at the end of the first one to schedule the second to run in 20 seconds instead of using Wait.

Please actually read the rules. Neither thread needs to be (or will be) deleted - you simply need to provide links to your cross-posts in future.

This is not very good code and probably shouldn't be used. It definitely shouldn't be used as a teaching tool other than for showing some things not to do.

Do you print the admin sheet? If not, it would be a good place to put the formula.

I would suggest you put a formula somewhere (eg on the Admin sheet) that checks if all the relevant cells have been filled in and returns TRUE/FALSE accordingly. Then in the ThisWorkbook module of the workbook, add:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = (Sheets("Admin").Range("Z1").Value = False)
End Sub

(I've assumed you use Z1 on the admin sheet for the formula)

Doing it this way means you only need to adjust the formula if the worksheet layout changes, not the code.

Carim

No worries.

Replace this:

With wkbsour
.Range(.Cells(lsrow, lscol)).Copy wkbdest.Cells(i + ldRow - 1, j + ldCol - 1)
End With

with this:

wkbsour.Cells(lsrow, lscol).Copy wkbdest.Cells(i + ldRow - 1, j + ldCol - 1)

I'd also suggest renaming the variable since it isn't a workbook object.

Clearly (or hopefully) that isn't the actual code since the range address is invalid. Based on what it looks like you're doing, and given that you didn't say what the error was, I'd guess that the address you are passing is longer than 255 characters, which Range won't accept. Either do it in chunks or use Union to create one range from separate ones.

Currently it will show the value from the active sheet - is that what you wanted?

It sounds like you may just want Cells(2, activecell.column) or something similar.