Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variable Constant (2002)

    Hi,
    I have a piece of code that sets a constant as follows:

    Const strApp = "Session A"

    My problem is that "Session A" can occasionally be "Session B". I have no idea how, or why.

    Anyway, I also have a userform set to open at workbook open. I would like to add the variable A or B as an input box, and then have the code read from the variable. Apologies if my terminoligy is all to shot, hopefully you will understand?

    I should be able to adjust the userform without too much hassle, but would appreciate a little help with the code adjustment to read the variable.

    thanks

  2. #2
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Constant (2002)

    Hans,
    I knew I was talking gibberish, but thanks for understanding!

    >Put a combo box cmbType on the userform. I have selected ComboBox from the toolbox but I am getting a compile error. {method or data member not found} at line: With Me.cmbType

    Also I have 3 variables in the userform, when I tab from the 2nd, it tab's to the command button, ignoring the combobox altogether.

    Also, there is nothing in the combobox to select, I must be doing something wrong.

    I have attached the code (in full) that is attached to the userform command button.
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable Constant (2002)

    If the value can change, you must use a variable, not a constant. The value of a constant is, well, er, uh, constant, meaning that it can't change.

    So declare strApp as follows in a standard module:

    Public strApp As String

    Put a combo box cmbType on the userform, and add the following code to the UserForm_Initialize event procedure:

    With Me.cmbType
    .AddItem "Session A"
    .AddItem "Session B"
    .ListIndex = 0
    End With

    And create the following code for the combo box:

    Private Sub cmbType_AfterUpdate()
    strApp = Me.cmbType
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable Constant (2002)

    The code

    With Me.cmbType
    .AddItem "Session A"
    .AddItem "Session B"
    .ListIndex = 0
    End With

    should be in the UserForm_Initialize procedure, not in the CommandButton1_Click procedure. (There was a typo in my previous reply, now corrected)

    You can specify the tab order of controls by selecting View | Tab Order. Only controls whose TabStop property is set to True will be included in the tab order.

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Constant (2002)

    Sorry Hans, [haircut] maybe my lack of vba knowledge is shining today....I'm struggling with where each section of the code should go. I have attached the full file.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable Constant (2002)

    It makes no sense to put the code that populates the combo box in the QueryClose event procedure - the combo box would be filled when the user closes the form...
    As I have written twice before, and now for the third time, you should put it in the Initialize event procedure.

    And it helps if you actually name the combo box cmbType. It is named ComboBox1 now, so cmbType is not recognized.
    Alternatively, you could have used ComboBox1 instead of cmbType in your code.

    The line

    strApp = Me.cmbType

    makes no sense in CopyBlocks, since that code is in a standard module. "Me" is only valid in a class module (such as a userform module). Simply remove that line from CopyBlocks.

    You repeatedly call CopyBlock, but the macro is named CopyBlocks. You need to be consistent!
    Moreover, CopyBlocks doesn't have an argument, so I don;t know what you want to accomplish.

    I have attached a modified version with a working userform. I can't make the rest of the code work since I don't know what it should do.
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Constant (2002)

    Thanks Hans. It all makes perfect sense (now). As for the Copyblocks, this was code that you assisted me with a while back, it does all work!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable Constant (2002)

    > it does all work!

    Not in the workbook that you attached!

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Constant (2002)

    Hans,
    I think I understand what you are saying, and If so, then you are correct. The Sub CopyBlocks() is the only one called and does work!! The 15 subs Copy 01 - Copy 15 (that I think you are refering to) are no longer called. These are the old individual copy macro's that were attached to 15 individual call button's, prior to the new one button Sub CopyBlocks() that completes the whole task. I will delete accordingly. <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> To be honest, I don't fully understand the code (that you kindly assisted with) and was not sure whether the sub's were still in some way related.

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Constant (2002)

    Following on from my last post, I was confused as to how the confusion started in the first place, so i went hunting back in time....

    The very long thread starting at <post#=717,755>post 717,755</post#> provided me with a piece of code <!post=CopyBlock with 15 individual calls,717798>CopyBlock with 15 individual calls<!/post> , which was later replaced with <!post=CopyBlocks - The one click miracle,717802>CopyBlocks - The one click miracle<!/post> . I left some of the first code there, when I switched to using the second code.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> That thread was never really closed, I left it pending as work in progress. I did EVENTUALLY, MIRACULOUSLY, get it to work, some time later, after a lot of trial and error, and a little help from a colleague. I will update it now with the solution, incase anyone has the same requirement in the future.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable Constant (2002)

    Thanks!

  12. #12
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Constant (2002)

    Hans,
    I am attempting to grow this from a capacity of 60 to a capacity of 400, and then possibly further. Please could you advise how the code (CopyBlocks) changes in relation to this. As I don't really understand this particular piece of code, would you mind commenting it, so that i understand and can make future tweaks.

    Due to the growth, the file is now too big (even zipped) to post, so I am attaching just the code. There are 100 blocks of 4 starting at B21:I30, then B33:I42 etc.

    Many Thanks
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable Constant (2002)

    You originally had 15 blocks, each of 1 to 4 transactions, hence the line

    For n = 1 To 15

    Since you want to extend it to 100 blocks, you should change this line to

    For n = 1 To 100

    The blocks are still 12 rows apart, so you should NOT change 12 to 22 in the line

    intStart = 12 * n - 1

    Instead, change the - 1 so that you start at the correct row. Your first block (n = 1) starts in row 21, so you must change - 1 to + 9:

    intStart = 12 * n + 9

    n = 1 -> intStart = 1 * 12 + 9 = 21
    n = 2 -> intStart = 2 * 12 + 9 = 33
    etc.

  14. #14
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Constant (2002)

    Thanks Hans. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  15. #15
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Constant (2002)

    Hans,
    Would you mind explaining how to set this up: "Only controls whose TabStop property is set to True will be included in the tab order."
    Thanks

Page 1 of 2 12 LastLast

Posting Permissions

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