my vba keeps saying run time error 9 - what does that mean?

[ACrossley1]

Hi.

I have a button from foam controls and linked a 'macro' on the button, so it takes me to a different excel document when i click the button.

I create the correct macro and assign it to the button. However, once i close the programme the macro stops working and comes up with a warning 'run time error '9'. subscript out of range.

Why is this happening? and how do i stop this?

Thanks.

[:) Sixthsense :)]

once i close the programme the macro stops working

What do you mean by "once i close the programme"

Please post your code.

[ACrossley1]

Hi.

I have two documents:
1 main excel document - where all my macros run to & 1 sub document (where these issues are coming from)

There are about 15-20 sub documents all leading back to this 1 main excel document.

When the main document is open all the macros run perfectly. Once the main document is closed however, the issues start and the macros do not work and come up with that 'run time error 9'

Im not sure what i can do.

ive tried to do them all again but they still dont work.

[:) Sixthsense :)]

Why don't you keep your codes in Personal Xlsb?

[ACrossley1]

Hi.

Im not sure how to do that.

Im not that good on excel, i just know a couple of simple things on the macros, so i link documents to different documents by pressing a simple button.

Not sure why this is not working. The name of the document it is supposed to 'acitivate' looks correct

[Andy Pope]

I would get your code only activates the workbook, which is fine if the workbook is already opened.
If you close the workbook and then try the code it will fail with error 9.

You need to check the workbook is open, open it if not, and then activate it.

Does your code look something like this, where Book2 is replaced with the name of your workbook?

Please Login or Register  to view this content.

[ACrossley1]

Hi.

Yeah it looks like that.
So the macro will only work if the document is open?

I thought it used to work when it wasnt open and would open the document up itself if needed.

[Andy Pope]

It would not automatically open the workbook unless the code explicitly did that. We have not seen your code so can not say exactly.

[ACrossley1]

Ahh right ok.

How would i write a macro whcih would open it if it was not already open?

An example is shown below:
Windows("Book2.xlsx").Activate
Sheets ("1").Select
Range ("B5").Select

[Andy Pope]

One approach

Please Login or Register  to view this content.

Is the tab name really "1" in your workbook?

[nams123]

i have a similar problem please help... it says "Run-time error subscript out of range"

code---

Sub UploadOrUpdate()

Const Tbl As String = "Query_Management_Temp"
Const sCell As String = "strt"

Dim oSht As Worksheet

Dim fstRow As Integer, _
lstRow As Integer
Dim fstCol As Integer, _
lstCol As Integer

Dim sHdrRange As String, _
DataRange As String

Dim InsertQuery As String, _
xlRow As Long, _
xlCol As Integer, _
tmp As String
Dim bFlag As Boolean: bFlag = False

Application.ScreenUpdating = False
Set oSht = ThisWorkbook.Sheets(ActiveSheet.Name)

''mdlSpinner.StartPreloader
On Error GoTo ErrHandler

Dim Arr() As String: Arr() = VBA.Split(Application.UserName, ",", -1, vbTextCompare)

modGlobal.UnprotectSht oSht

If oSht.Shapes(Application.Caller).TextFrame2.TextRange.Characters.Text = "Submit New Query" Then GenerateQueryRefrenceNumber: bFlag = True

fstRow = Range(sCell).Row
lstRow = oSht.Cells(Application.Rows.Count, Range(sCell).Column).End(xlUp).Row
fstCol = Range(sCell).Column
lstCol = oSht.Cells(Range(sCell).Row, Application.Columns.Count).End(xlToLeft).Column

sHdrRange = Cells(fstRow, fstCol).Address & ":" & Cells(fstRow, lstCol).Address

DataRange = "[" & oSht.Name & "$" & oSht.Range(Cells(fstRow, fstCol).Address, Cells(lstRow, lstCol).Address).Address(0, 0) & "]"

Dim sSql As String: sSql = GetExlQuery(oSht, sHdrRange, DataRange)
Dim oRs As ADODB.Recordset

InitDatabase "Excel", "", ThisWorkbook.FullName, "", "", True

Set oRs = gobjDB.GetRecordset(sSql)

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Temp").Delete
ThisWorkbook.Sheets.Add
ThisWorkbook.ActiveSheet.Name = "Temp"
On Error GoTo 0

If Not oRs.EOF Then
RecordsetToExcelSheet oRs, Sheets("Temp"), 1, 1 ''1-Row Position & 1-Column Number
ThisWorkbook.Sheets("Temp").Cells.WrapText = False
End If

DestroyDatabase

xlRow = 2
With ThisWorkbook.Sheets("Temp")
While .Cells(xlRow, 1) <> ""

For xlCol = 1 To .UsedRange.Columns.Count 'Must match the table structure
If tmp = vbNullString Then
tmp = "('" & IIf(Replace(.Cells(xlRow, xlCol), "'", "`") = vbNullString, "NULL", Replace(.Cells(xlRow, xlCol), "'", "`")) & "'"
Else
tmp = tmp & ",'" & IIf(Replace(.Cells(xlRow, xlCol), "'", "`") = vbNullString, "NULL", Replace(.Cells(xlRow, xlCol), "'", "`")) & "'"
End If
Next xlCol

If xlRow = .UsedRange.Rows.Count Then
If InsertQuery = vbNullString Then InsertQuery = tmp & ")" & vbNewLine Else InsertQuery = InsertQuery & tmp & ")" & vbNewLine
Else
If InsertQuery = vbNullString Then InsertQuery = tmp & ")," & vbNewLine Else InsertQuery = InsertQuery & tmp & ")," & vbNewLine
End If

tmp = vbNullString
xlRow = xlRow + 1
Wend

''Debug.Print "INSERT INTO " & Tbl & " VALUES " & InsertQuery
InsertQuery = VBA.Replace(InsertQuery, "'NULL'", "NULL", 1, -1, vbTextCompare)
''Debug.Print "INSERT INTO " & Tbl & " VALUES " & InsertQuery

End With
If Not InsertQuery = vbNullString Then

modGlobal.InitDatabase "SQLServer", sServerName, sDatabaseName, sUserID, sPassword

gobjDB.ExecuteActionQuery ("TRUNCATE TABLE [Reporting].[dbo].[Query_Management_Temp]")

gobjDB.ExecuteActionQuery ("INSERT INTO " & Tbl & " VALUES " & InsertQuery)

If oSht.Shapes(Application.Caller).TextFrame2.TextRange.Characters.Text = "Submit New Query" Then
oSht.Range("A7:AZ100000").ClearContents
''sSql = "EXEC Reporting..[Sp_Query_Management_Insert] '" & VBA.Trim(Arr(UBound(Arr))) & " " & VBA.Trim(Arr(LBound(Arr))) & "','" & VBA.Format(Now, "YYYY-MM-DD HH:MM:SS AM/PM") & "'"
sSql = "EXEC Reporting..[Sp_Query_Management_Insert] '" & VBA.Trim(Environ("UserName")) & "','" & VBA.Format(Now, "YYYY-MM-DD HH:MM:SS AM/PM") & "'"
Else
''sSql = "EXEC Reporting..[Sp_Query_Management_Update] '" & VBA.Trim(Arr(UBound(Arr))) & " " & VBA.Trim(Arr(LBound(Arr))) & "','" & VBA.Format(Now, "YYYY-MM-DD HH:MM:SS AM/PM") & "'"
sSql = "EXEC Reporting..[Sp_Query_Management_Update] '" & VBA.Trim(Environ("UserName")) & "','" & VBA.Format(Now, "YYYY-MM-DD HH:MM:SS AM/PM") & "'"

End If

gobjDB.ExecuteActionQuery (sSql)

modGlobal.DestroyDatabase

''mdlSpinner.FinishPreloader

If bFlag = True Then
MsgBox "Submission of new queries was successful.", vbInformation + vbOKOnly, "New Queries Submission"
Else
MsgBox "Queries update was successful.", vbInformation + vbOKOnly, "Update Existing"
End If

End If

ErrHandler:
'newsteps
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Temp").Delete

Application.DisplayAlerts = True
On Error GoTo 0

Range(sCell).Activate

modGlobal.ProtectSht oSht

''mdlSpinner.FinishPreloader

Set oSht = Nothing
Application.ScreenUpdating = True


End Sub

[Pepe Le Mokko]

@nams

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread