Results 1 to 11 of 11

Thread: Global Variable

  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Global Variable

    Hi Experts,

    I've haven't coded for a number of months. I'm amazed at how much I've forgotten. It must be my senior moments.

    I have a simple form attached. I am trying to use RowCount to move down 1 row at a time. Isn't working.

    Thanks in advance for your help.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    ..have a look at this version.

    zeddy
    Attached Files Attached Files

  3. The Following User Says Thank You to zeddy For This Useful Post:

    Excelnewbie (2016-11-29)

  4. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Thanks zeddy-

    Works as I had hoped.

    Question: Is it necessary to use a module or can everything be in the same project window if desired?

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Excel,

    Here is a twist in the code. Open the form using the button zeddy created. When the code cycles through all of the rows, the form automatically closes. By toggling between the two lines of code (commenting one while uncommenting the other) you can make the code remember the point you left off when you closed the form. All code is located in the Sheet module and no global variable required

    HTH,
    Maud

    In the sheet module
    Code:
    Private Sub CommandButton1_Click()
    Me.Hide  'UNCOMMENT TO REMEMBER STARTING POINT
    'Unload Me  'UNCOMMENT TO ALWAYS START AT BEGINNING ROW
    End Sub
    
    Private Sub Read_Click()
    Dim rng As Range, x as Integer
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range("A1:A" & LastRow)
    x = Read.ControlTipText
    FruitTextBox.Value = rng(x)
    UserForm1.ColorTextBox.Value = rng(x).Offset(0, 1)
    Read.ControlTipText = x + 1
    If Read.ControlTipText > rng.Count + 1 Then
        Read.ControlTipText = 1
        Me.Hide
    End If
    End Sub
    
    Private Sub UserForm_Initialize()
    Read.ControlTipText = 1
    End Sub
    Attached Files Attached Files

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2016-11-29)

  7. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maud,

    Wonderful! Thanks for your input. I will enjoy studing the code.

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Question: Is it necessary to use a module or can everything be in the same project window if desired?
    ..to answer your question:
    You don't have to use a module.
    The code in your form module can call subroutines in other modules.

    In Excel VBA, there are four main types of modules:*
    1. Standard Code Modules (which contain custom macros and functions)
    2. Workbook And Sheet Code Modules (event routines)
    3. User Forms (which contain code for the controls on a UserForm object)
    4. Class Modules (which contain Property Let, Get, and Set procedures for Objects that you create)

    Generally, I prefer to put as much code as possible into well-named Standard Code modules, and only put the minimum code you need into the others.

    zeddy

  9. The Following User Says Thank You to zeddy For This Useful Post:

    Excelnewbie (2016-12-01)

  10. #7
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    zeddy,

    Thank you for taking the time to answer my question. I appreciate it.

  11. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    I appreciate the replies too!

    Now, one advantage of coding within the Form's code module is that you can use the Me keyword to refer to the Form itself. But I still prefer to use a standard vba module in most cases. Except when I don't.

    I can see that you are already familiar with the Properties window in the VBA Editor, as you have given your own names to some of the Form's Textbox controls. You can also use the Properties window to give your own names to the Forms themselves e.g. instead of Userform1 you can assign a name e.g. formFruit
    (You can also use the Properties window to give names to your standard vba modules, e.g. like modMain, modStartup, modImport, modEmailer etc etc etc)

    When you want to refer to a form's control in a routine in a standard vba modue, you would prefix the control with the form's name:
    e.g. Userform1.Textbox2 or, for example, formFruit.FruitTextBox etc etc etc

    zeddy

  12. #9
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    zeddy,

    Thanks again. Your information is very helpful.

  13. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    ..but remember Newton's Law:

    "for every expert there is an equal and opposite expert"

    zeddy

  14. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    In the sheet module
    Correction Post #4

    Should have read: In the Form Module.

    Maud

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •