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]

I'd need to see the file. If your original code worked in the SiteList sheet module, it should work with the alterations I suggested. Let's try and narrow it down using this code:

Private Sub CreatePPA()
DIm cb as MSForms.Combobox
Set cb = Sheets("SiteList").sitelist
Dim selectedRow as long
selectedRow = cb.ListIndex + 2 
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" & selectedRow) & "?")
generation = Sheets("SiteList").Range("I" & selectedRow) - myValue * Sheets("SiteList").Range("I" & selectedRow)
If myValue < Sheets("SiteList").Range("K" & selectedRow) Then
MsgBox "At least " & Sheets("SiteList").Range("K" & selectedRow) & " turbine needs to be off before a PPA is required"
Else
answer = MsgBox("you have up to " & Sheets("SiteList").Range("L" & selectedRow) & " 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

 

[jmcconnell]

Hi Rory. Thank you so much for your help. Using your code above it's working perfectly. Thank you again for all your help (And patience)