Userform Value

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Userform Value

Post by adeel1 »

Hello All
sorry about my English
mY issue is when i call code each other(from Sheet2 to userform or from userform to sheet2) C1 and C2 gets blanks, how can i remains store c1 and C2 value for work. sample attached for understanding in real these values will go to another WBK.

below Code in CommandButton1

Code: Select all

Private Sub CommandButton1_Click()
Select Case Me.CheckBox1

Case True
C1 = "yes"

'Range("A2").Resize(10) = C1
End Select


Select Case Me.CheckBox2

Case True
C2 = "yes"
End Select

Unload Me
Sheet2.Fi_l
End Sub
this code is in sheet2

Code: Select all

Sub Fi_l()


Range("A2").Resize(10) = C1
Range("B2").Resize(10) = C2
End Sub
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Userform Value

Post by HansV »

1) You don't declare C1 and C2, so they are local to the procedure they are in. You must declare them as public variables in a standard module such as Module1:

Code: Select all

Public C1 As String
Public C2 As String
2) You should not call Act_ive from Fi_l.

See The importance of 'Option Explicit' and A Dim understanding: declaring variables in VB/VBA

Sample for Eli.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Userform Value

Post by adeel1 »

thnx for your reply and provided links will go through deep bit later will ask question if i have.

for now just one question, "public" will always declare for userfrom means storing value either within WBK or for Other WBK.

Adeel

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Userform Value

Post by HansV »

Public means that the variable will be "known" in all code modules of the same workbook, but not in code modules in other open workbooks. See the second link in my previous reply for more details.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Userform Value

Post by adeel1 »

i will go through and Thnx for Help. :clapping: :chocciebar:

Adeel

User avatar
DocAElstein
4StarLounger
Posts: 542
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Pseudo Public variables in other Workbook

Post by DocAElstein »

Hello,
This is my second attempt. I was about to post this in a slightly more complicated form, when I noticed I could do it a lot easier and simpler. But there was some interesting, if over complicated, stuff in that First overcomplicated solution, that might be useful for future reference, so I archived it for prosperity anyway…
https://excelfox.com/forum/showthread.p ... #post16649
_.______________________________________________________________________________________________________________



Hello again!
adeel1 wrote:
15 Jul 2022, 12:42
.... in real these values will go to another WBK.
:::
or now just one question, "public" will always declare for userfrom means storing value either within WBK or for Other WBK.
HansV wrote:
15 Jul 2022, 13:28
Public means that the variable will be "known" in all code modules of the same workbook, but not in code modules in other open workbooks. .
If Public variables are being the things that go in a normal code module, as I think they are, technically, or officially, or according to most people, or words to that effect, ….. then that is the case that they won’t be known in other workbooks.
In other words, for the purposes of what is going on here, it means you will need to be storing them in the same workbook, as Hans said, and how Hans demonstrated.

However, you can do something that technically is not involving Public variables, but as far as I can tell, to all intents and purposes, is in effect the same thing, or very similar to, as if you could have those Public variables in a different workbook.


The short story is:
Instead of putting the two public variables in a standard normal code module, ( in the same workbook) as Hans did, we can put them in any Class object code module in any open workbook. ( Technically they are not called Public variables. They are , I think, properties of the instantiated Class object, and we can access them, in the usual way that we access properties of an object.
I call them Pseudo Public variables, just because I want to, and because I can , as I just did! )


The full story
Workbook PurseWayDoughPublicVariables.xls

I have another workbook uploaded, PurseWayDoughPublicVariables.xls . That is just to hold these Pseudo Public variables
I can put them Pseudo Public variables in any Class object code module, in any open workbook, but just for fun, I will put C1 in a worksheet code module, and C2 in the ThisWorkbook code module, both in the workbook PurseWayDoughPublicVariables.xls
So, this is what Hans did, Proper Public variables in a standard normal module like
Standard module, Module1 ( in Sample for Eli.xlsm )

Code: Select all

Public C1 As String
Public C2 As String
Instead of doing that , I will put those variables in Class object code modules in PurseWayDoughPublicVariables.xls, like this:

Worksheet code module, Sheet1 ( in PurseWayDoughPublicVariables.xls )

Code: Select all

 Public C1 As String
Workbook code module, ThisWorkbook ( in PurseWayDoughPublicVariables.xls )

Code: Select all

 Public C2 As String

Workbook Sample for Eli.xlsm
I need to modify now the workbook uploaded by Hans, in 3 main ways:
_(i) I don’t need the two public variables in a standard normal code module anymore – ( I showed above where I now have them instead )
_(ii) I need to modify slightly how I reference the variables

Code: Select all

 Sub Fi_l()
    'Act_ive
 'Let Range("A2").Resize(10).Value = C1
 Let Range("A2").Resize(10).Value = Workbooks("PurseWayDoughPublicVariables.xls").Worksheets("Sheet1").C1
 'let Range("B2").Resize(10).Value = C2
 Let Range("B2").Resize(10).Value = Workbooks("PurseWayDoughPublicVariables.xls").C2
End Sub
_(iii) I need to fill the variables in a slightly different way

Code: Select all

Private Sub CommandButton1_Click()
    Select Case Me.CheckBox1
     Case True
      'C1 = "yes"
     Let Workbooks("PurseWayDoughPublicVariables.xls").Worksheets("Sheet1").C1 = "Yus"
    End Select
    Select Case Me.CheckBox2
     Case True
      'C2 = "yes"
     Let Workbooks("PurseWayDoughPublicVariables.xls").C2 = "Ja"
    End Select
 Unload Me
 Call Sheet2.Fi_l
End Sub



That’s it.
So download both files, open them both, and then the coding in Sample for Eli.xlsm should work as before. The only difference is that you are using the “pseudo” public variables in the workbook PurseWayDoughPublicVariables.xls

_._____________________________________________________________________


I have not seen this use of “pseudo” public variables much before, so there may be some reason I don’t know about why they should not be used??
But I use them myself sometimes, and so far I have never seen them behave much differently to “proper” public variables. ###

( I would just finally say that I don’t use public variables much myself, pseudo or otherwise, if I can find another way to do what I want. I don’t like public variables myself. For one reason: I find they have an annoying habit of getting emptied sometimes. ( ### One small advantage of using a pseudo Public variable in another workbook is that it won't get emptied as a proper one would in the main workbook, if for example a macro errors , since that typically resets the VBA project in the workbook that errors, which empties global variables)
For what is required here, I would simply store the variables in some spare cells in a worksheet somewhere.
For large files, I often have a worksheet called something like “MeGlobies”, and anything like those variable values are stored there, along with other temporary stuff. I find that can be useful when debugging. Often then, I have an event code that kicks in on closing that deletes that worksheet. If I don't delete it, then it can be used as a bit of a Log, and often includes a few dates of when stuff got done )




Alan
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Userform Value

Post by adeel1 »

thnx Alan for your knowledge and help on this issue.