Code works until I try and place it in a module

jmcconnell

New Member
Joined
Feb 2, 2019
Messages
29
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:
Code:
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
 
Last edited by a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,640
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

VBA 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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

jmcconnell

New Member
Joined
Feb 2, 2019
Messages
29
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,112,995
Messages
5,543,185
Members
410,584
Latest member
Bluefox68
Top