MsgBox Error

jreed5535

New Member
Joined
Jul 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hi Guys,

I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2.
1595988608802.png



The only difference is the MsgBox goes into capitals
1595988719903.png


1595988788418.png


any ideas why this is happening?

Cheers
 

jreed5535

New Member
Joined
Jul 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Another question, is there away i can put in a code to click exit on the pop up box when opening a file? like the below.

1595991959189.png


thanks again
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
Something like this will do it
UpdateLinks:=0 won't update
UpdateLinks:=1 will update

VBA Code:
Set mybook = Workbooks.Open(Filename:=MyPath & MyFiles(FNum), UpdateLinks:=0)
 

jreed5535

New Member
Joined
Jul 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Thanks Michael, works perfect!
I have one last code i am trying to do but cannot figure it out.

I want to insert a blank row 2 rows under a cell if that cell meets a criteria (i will need to link the criteria to a cell in a different sheet as it will change) then copy and paste the row above, then copy and values paste a value from a cell in another worksheet.

1595993866535.png


So want it so find ..17MINE then insert a row 2 rows under it.

1595994025138.png


it will find where to insert the row based on D2 value
then copy the formulas from the above cell (could maybe just copy the row 1 below the ..17MINE then insert copied cell)
then values paste B2 into a cell in the row just added.

Hope this makes sense.

Thanks again
 

Attachments

  • 1595993958730.png
    1595993958730.png
    6.4 KB · Views: 0

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
this will put a insert the row 2 rows below
VBA Code:
Sub MM1()
Dim r As Long
For r = 2 To Cells(Rows.Count, "D").End(xlUp).Row
   If Cells(r, 4).Value = "..17MINE" Then Cells(r + 2, 4).EntireRow.Insert
Next r
End Sub
 

jreed5535

New Member
Joined
Jul 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

is there anyway to have it link to a cell in another worksheet instead of Value = "..17MINE"?
 

jreed5535

New Member
Joined
Jul 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

so have something like this

If Cells(r, 4).Value = worksheets ("sheet1"). range("D2"). Then Cells(r + 2, 4).EntireRow.Insert

So if D2 changes it will look for that value instead.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
Like this ??
VBA Code:
Sub MM1()
Dim r As Long, ws As Long
Set ws = Sheets("Sheet1")
For r = 2 To Cells(Rows.Count, "D").End(xlUp).Row
If Cells(r, 4).Value = ws.Cells(2, 4).Value Then   Cells(r + 2, 4).EntireRow.Insert
Next r
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
OOPs, sorry.
Change the 2nd line to
VBA Code:
Dim r As Long, ws As Worksheet
 

Watch MrExcel Video

Forum statistics

Threads
1,108,599
Messages
5,523,815
Members
409,537
Latest member
AlfredSiauw

This Week's Hot Topics

Top