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,652
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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,114,191
Messages
5,546,476
Members
410,742
Latest member
WalterSil
Top