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:

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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
You need to refer to it as a member of that sheet, using Sheets("sheet name").sitelist.ListIndex
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,007
Office Version
  1. 2019
Platform
  1. Windows
Sitelist is an object on a sheet so you need to specify which sheet
VBA Code:
 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

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

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

ADVERTISEMENT

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

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

jmcconnell

New Member
Joined
Feb 2, 2019
Messages
29
Listindex returns a number, not an object. What I meant was to use:

Rich (BB code):
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

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

ADVERTISEMENT

Yes. :)
 

jmcconnell

New Member
Joined
Feb 2, 2019
Messages
29
@RoryA Thank you. I've updated code as below but I'm still getting the same issue:
VBA Code:
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
 
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
If you're getting a 'variable not declared' error, it's probably because you haven't declared any of your variables. :) You should add:

Code:
Dim ChromeLocation as string
Dim myValue, answer

to the start of the code.
 

jmcconnell

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

Watch MrExcel Video

Forum statistics

Threads
1,114,193
Messages
5,546,479
Members
410,742
Latest member
WalterSil
Top