Code works until I try and place it in a module

jmcconnell

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

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
You need to refer to it as a member of that sheet, using Sheets("sheet name").sitelist.ListIndex
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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) & "?")
 
Upvote 0
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.
 
Upvote 0
@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:
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top