CODEWITHSUNDEEP
vbaexcel
Yong
Yong

Reputation: 191

Passing a value from UserForm to sheet

I created a userform called Level with a button, "beginner". When it's clicked "beginner" is set as levelinput as a string to be used in coding sheet 1

--------------Sheet1(Code)------------

Public levelInput as string

Public Sub Player()
Level.show
Msgbox levelInput

Lev=levelInput
.....

end sub

--------------Below is for Userform, Levels------------------

Private Sub beginner_Click()
levelInput= "beginner"
Levels.Hide
end sub

Currently, the msgbox shows nothing and the value doesn't seem to be passed to the Sheet(Code).

Where did I go wrong?

Upvotes: 1

Views: 721

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

levelInput= "beginner"

That variable isn't declared. I know, you think it is, but I'll get to it in a moment.

The thing you've fallen pray of, is called scoping.

Public levelInput as string

By declaring the levelInput public field in the code-behind of Sheet1, you're saying "any instance of Sheet1 will have a levelInput public variable".

So you can change your code to this:

Sheet1.levelInput= "beginner"

And it will work.

But the most important thing to do is this:

Option Explicit

Always turn that option on, in the declarations section of every module (i.e. at the top, before any Sub or Function or Property). Had you done that, the VBA compiler would have told you levelInput isn't declared, and would have refused to run anything until you fixed the problem - either by declaring a locally-scoped levelInput variable, or by properly qualifying the Sheet1.levelInput public field.

Without Option Explicit, VBA happily lets you assign and refer to variables that aren't declared, which leads to unexpected bug, as you've experienced.

PS - Once you get your code to work as intended, I'd recommend you post it on Code Review, where you'll learn things like why Level.Show is bad code, and how you can use functions, parameters and return values instead of global variables, for more robust, maintainable and easier-to-follow code.

Upvotes: 1

Related Questions