It's not really the "option explicit" that's causing the trouble. It's the
location of the Public declarations.

That line is a directive to VBA that you want it to yell at you if you don't
declare any of the variables you use in that module.

It's a very good idea to have "Option Explicit" at the top of each and every
module--the userform modules, the worksheet modules, the ThisWorkbook module,
and the plain old general modules.



Madduck wrote:

It worked...

its all about the location of my explicit :)

Thanks heaps for the help...

"Dave Peterson" wrote:

How/where did you declare the GemValue variable?

Did you put it in that same general module along with GemRowLoc and GemColLoc?

Option Explicit
Public GemRowLoc As Long
Public GemColLoc As Long
Dim GemValue As Long



Madduck wrote:

Thanks Guys,

I have changed the varible names as suggested...
This script is far from complete, as I'm learning as I go....

as such I have a few "test outputs"

from the form "Gemlist" I can output Gemname to a cell.

What I can't seem to do is pass the value back to the main code...

I did not want to paste the whole code , and clog up the forums, but here is
the entire code behind the form "gemlist"

Sub SubmitButton1_Click()
Sheets("Rift_raid").Select

Gemid

Cells(GemRowloc, GemColloc).Value = Gemname.Value
Range("o1") = ""
Gemname.Value = ""
Gemname.Clear
Gemlist.Hide

End Sub
Sub Gemid()

If Gemname.Value = "Boots" Then
Gemvalue = 1
ElseIf Gemname.Value = "Gloves" Then
Gemvalue = 2
ElseIf Gemname.Value = "Leggings" Then
Gemvalue = 3
ElseIf Gemname.Value = "Chest" Then
Gemvalue = 4
ElseIf Gemname.Value = "Helm" Then
Gemvalue = 5
Else
Gemvalue = 6
End If
Range("o1") = Gemvalue ' for transporting the value out of the subroutine

End Sub

Private Sub userform_Activate()
Gemname.AddItem ("Boots")
Gemname.AddItem ("Gloves")
Gemname.AddItem ("Leggins")
Gemname.AddItem ("Chest")
Gemname.AddItem ("Helm")
Gemname.AddItem ("Shoulders")
Gemname.AddItem ("Weapon")
End Sub

--

Dave Peterson


--

Dave Peterson