Code works until I try and place it in a module

[jmcconnell]

The code below works perfectly when placed in a worksheet however i'd rather it was in a module. As soon as I put it in a module and try to run it i get an error:

Option Explicit
'Declarations
Dim OutApp As Object
Dim OutMail As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object
Dim myValue As Double
Dim answer As Double
Dim generation As Double
Dim ChromeLocation As String

Private Sub CreatePPA()
ChromeLocation = "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" 'Location of Chrome.exe in your PC

myValue = InputBox("How many turbines are off at " & Sheets("SiteList").Range("A" & sitelist.ListIndex + 2) & "?")
generation = Sheets("SiteList").Range("I" & sitelist.ListIndex + 2) - myValue * Sheets("SiteList").Range("J" & sitelist.ListIndex + 2)
If myValue < Sheets("SiteList").Range("K" & sitelist.ListIndex + 2) Then
MsgBox "At least " & Sheets("SiteList").Range("K" & sitelist.ListIndex + 2) & " turbine needs to be off before a PPA is required"
ElseIf myValue >= Sheets("SiteList").Range("K" & sitelist.ListIndex + 2) Then
answer = MsgBox("you have up to " & Sheets("SiteList").Range("L" & sitelist.ListIndex + 2) & " hours to send a PPA.  Would you like to send one now?", vbYesNo + vbQuestion)
End If
If answer = vbYes Then

If Sheets("SiteList").Range("F" & sitelist.ListIndex + 2).Hyperlinks.Count > 0 Then
Shell (ChromeLocation & " -url " & Sheets("SiteList").Range("F" & sitelist.ListIndex + 2))

I get the error "Variable not found". It highlights 'Sitelist' (shown in red above) which is a combobox. I don't get this error when the code is placed in the same worksheet as the combobox but i'm unsure how to get it to run in a module?

Any help would be much appreciated.

Kind regards,
James

 

[RoryA]

You need to refer to it as a member of that sheet, using Sheets("sheet name").sitelist.ListIndex

 

[jasonb75]

Sitelist is an object on a sheet so you need to specify which sheet

 myValue = InputBox("How many turbines are off at " & Sheets("SiteList").Range("A" & Sheets("SiteList").sitelist.ListIndex + 2) & "?")

although I'm not entirely convinced that it will work that way without having something to test it on.

Unless you have a good reason for not having it in the worksheet module it would be better to leave it there.

 

[jmcconnell]

Hi all thanks for the help.

@RoryA. I'm fairly new to this would you be able to expand on what I need to do.

Sheets("sheet name").sitelist.ListIndex

The sheet name is also called Sitelist.

So i tried to add:

Dim sitelist as combobox
Set sitelist = Sheets("sitelist").sitelist.listindex

but this is not supported. Would you be able to give me any further advice?

Thanks again.

 

[RoryA]

Listindex returns a number, not an object. What I meant was to use:

myValue = InputBox("How many turbines are off at " & Sheets("SiteList").Range("A" & Sheets("SiteList").sitelist.ListIndex + 2) & "?")

 

[jmcconnell]

Listindex returns a number, not an object. What I meant was to use:

myValue = InputBox("How many turbines are off at " & Sheets("SiteList").Range("A" & Sheets("SiteList").sitelist.ListIndex + 2) & "?")

Hi @RoryA, your code still mentions 'List index' above. Is this correct?

Thank you once again.

 

[RoryA]

Yes.

 

[jmcconnell]

@RoryA Thank you. I've updated code as below but I'm still getting the same issue:

Private Sub CreatePPA()


ChromeLocation = "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" 'Location of Chrome.exe in your PC

myValue = InputBox("How many turbines are off at " & Sheets("SiteList").Range("A" & Sheets("SiteList").sitelist.ListIndex + 2) & "?")
generation = Sheets("SiteList").Range("I" & Sheets("SiteList").sitelist.ListIndex + 2) - myValue * Sheets("SiteList").Range("I" & Sheets("SiteList").sitelist.ListIndex + 2)
If myValue < Sheets("SiteList").Range("K" & Sheets("SiteList").sitelist.ListIndex + 2) Then
MsgBox "At least " & Sheets("SiteList").Range("K" & Sheets("SiteList").sitelist.ListIndex + 2) & " turbine needs to be off before a PPA is required"
ElseIf myValue >= Sheets("SiteList").Range("K" & Sheets("SiteList").sitelist.ListIndex + 2) Then
answer = MsgBox("you have up to " & Sheets("SiteList").Range("L" & Sheets("SiteList").sitelist.ListIndex + 2) & " hours to send a PPA.  Would you like to send one now?", vbYesNo + vbQuestion)
End If
If answer = vbYes Then

MsgBox "It works"

End If

End Sub

 

[RoryA]

If you're getting a 'variable not declared' error, it's probably because you haven't declared any of your variables. You should add:

Dim ChromeLocation as string
Dim myValue, answer

to the start of the code.

 

[jmcconnell]

Thanks @RoryA. I had declared them. The error is 'Object doesn't support this property or method. even if I simplify it as below I still get the same:

MsgBox Sheets("SiteList").Range("A" & Sheets("SiteList").sitelist.ListIndex + 2)