Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Mastermind

  1. #1
    Lounger
    Join Date
    Oct 2007
    Location
    Wirral, UK
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have written a VBA program to play the code breaking game Mastermind in Excel 2003. No pretty graphics but it works.

    [attachment=85627:Mastermind.jpg]

    I'm now at the stage of adding the bells, knobs and whistles and I'm having a few problems. I'll post them one at a time.

    The first problem is the 11 command buttons. I have added these physically by drop/stretching from the controls tool box, but I think it would be better to generate them from code.

    I switched on Record Macro and watched what happened as I dragged a new button onto a spreadsheet and altered its properties. Everything was fine up to the point when I had stretched out the button to its required size. Excel generated code something like:

    Sheet1.OLEObjects.Add(Classtype:="Forms.CommandBut ton.1", Link:=False, DisplayAsIcon:=False, _
    Left:=20, Top:=306, Width:=60.75, Height:=21)

    But when I selected the button and started to change its properties, no new code appeared in the IDE window despite the fact the macro was still running.

    I copied the above code and set it up as my own macro but added my own properties, something like:

    Sheet1.OLEObjects.Add(Classtype:="Forms.CommandBut ton.1", Link:=False, DisplayAsIcon:=False, _
    Left:=20, Top:=306, Width:=60.75, Height:=21, Name:="Try1", Caption:="Try", _
    Font:="Kartika", Backcolor:=&H00FFFF80& )

    But it didn't work. VBA always came back with the message "Run time error '448'; Named argument not found". I tried reducing the new parameters one at a time but to no avail. I always got the error message.

    Does this mean I can't add the buttons dynamically?
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, you can add command buttons dynamically, but you can't arbitrarily add arguments to OLEObjects.Add.

    Try code like this:

    Code:
    Dim obj As OLEObject
    Set obj = Sheet1.OLEObjects.Add(Classtype:="Forms.CommandButton.1", _
      Link:=False, DisplayAsIcon:=False, _
      Left:=20, Top:=306, Width:=60.75, Height:=21)
    With obj
      .Name = "Try1"
      .Object.Caption = "Try"
      .Object.Font.Name = "Kartika"
      .Object.BackColor = &HFFFF80
    End With

  3. #3
    Lounger
    Join Date
    Oct 2007
    Location
    Wirral, UK
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794290' date='21-Sep-2009 16:35']Yes, you can add command buttons dynamically, but you can't arbitrarily add arguments to OLEObjects.Add.

    Try code like this:

    Code:
    Dim obj As OLEObject
    Set obj = Sheet1.OLEObjects.Add(Classtype:="Forms.CommandButton.1", _
      Link:=False, DisplayAsIcon:=False, _
      Left:=20, Top:=306, Width:=60.75, Height:=21)
    With obj
      .Name = "Try1"
      .Object.Caption = "Try"
      .Object.Font.Name = "Kartika"
      .Object.BackColor = &HFFFF80
    End With
    [/quote]

    Thanks Hans, but do I have to do something else, like embed the new button into the sheet? I ran this code as a macro and nothing appeared on Sheet1. The code seems to have executed though, as the Intermediate Window shows the object Sheet1.Try1 exists and e.g. has attribute Visible = True

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In this code, Sheet1 is the code name of the worksheet; this is not necessarily the same as the name displayed on the worksheet tab. If you want to apply the code to a sheet whose tab name is Sheet1, change

    Set obj = Sheet1.OLEObjects.Add(...

    to

    Set obj = Worksheets("Sheet1").OLEObjects.Add(...

  5. #5
    Lounger
    Join Date
    Oct 2007
    Location
    Wirral, UK
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794310' date='21-Sep-2009 20:00']In this code, Sheet1 is the code name of the worksheet; this is not necessarily the same as the name displayed on the worksheet tab. If you want to apply the code to a sheet whose tab name is Sheet1, change

    Set obj = Sheet1.OLEObjects.Add(...

    to

    Set obj = Worksheets("Sheet1").OLEObjects.Add(...[/quote]

    Working fine now, thanks!

    I've just thought of another button related issue. If you look at the picture you can see there are 9 buttons called 'Try' which are doing pretty much the same thing. I set these up as individual objects with unique names (Try1 thru Try9). I haven't done pure VB (VB6) for some time but I know you can give controls the same name and index them, potentially simplifying code. If I remember rightly there was an Index property available on command buttons and other controls. No Index property appears on Excel's command buttons. Can I assume this means I can't use indexing in VBA?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You're correct, there is no VBA equivalent for this VB6 feature.

  7. #7
    Lounger
    Join Date
    Oct 2007
    Location
    Wirral, UK
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Unfinished business, Mr Gates!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Better tell it to mr Ballmer

  9. #9
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    [quote name='Treecreeper' post='794322' date='21-Sep-2009 15:30']Unfinished business, Mr Gates![/quote]
    The comments period for requesting a change to VBA probably ended about 13 years ago!

    VBA is essentially a dead language. The only reason it still exists (and probably will continue to do so for quite some time) is the enormous installed base.

  10. #10
    Lounger
    Join Date
    Oct 2007
    Location
    Wirral, UK
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Gary Frieder' post='794476' date='22-Sep-2009 22:46']The comments period for requesting a change to VBA probably ended about 13 years ago!

    VBA is essentially a dead language. The only reason it still exists (and probably will continue to do so for quite some time) is the enormous installed base.[/quote]

    I'm not sure I believe in dead languages. When I started work in 1979 I was told Cobol was on the way out. When I retired from the job in 2008, guess what I was programming in?

  11. #11
    Lounger
    Join Date
    Oct 2007
    Location
    Wirral, UK
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I added code to Workbook_Open to generate several buttons as suggested, i.e.

    Code:
    Dim objTry1 As OLEObject
    Dim objTry2 As OLEObject
    ...etc...
    .
    AddTry1
    AddTry2
    ....etc...which executed...
    .
    Public Sub AddTry1()
    	Set objTry1 = Worksheets("Sheet1").OLEObjects.Add(Classtype:="Forms.CommandButton.1", _
    		Link:=False, DisplayAsIcon:=False, _
    		Left:=19.5, Top:=342, Width:=60.75, Height:=21)
    	With objTry1
    		.Name = "Try1"
    		.Object.Caption = "Try"
    		.Object.Font.Name = "Kartika"
    		.Object.Font.Bold = True
    		.Object.Font.Size = 14
    		.Object.BackColor = &HFFFF80
    	End With
    End Sub
    
    Public Sub AddTry2()
    	Set objTry2 = Worksheets("Sheet1").OLEObjects.Add(Classtype:="Forms.CommandButton.1", _
    		Link:=False, DisplayAsIcon:=False, _
    		Left:=19.5, Top:=306, Width:=60.75, Height:=21)
    	With objTry2
    		.Name = "Try2"
    		.Object.Caption = "Try"
    		.Object.Font.Name = "Kartika"
    		.Object.Font.Bold = True
    		.Object.Font.Size = 14
    		.Object.BackColor = &HFFFF80
    	End With
    End Sub
    
    ...and so on
    That worked fine until I closed the spreadsheet and restarted. The buttons generated previously were still there, so when Worksheet_Open executed again it created a new set of buttons which it placed on top of the first set. It recognised the potential duplication of Name and called them CommandButton1, CommandButton2, etc. (I'm amazed it didn't abend).

    I thought I might get round this by deleting the buttons' objects on exit so I coded

    Code:
    Set objTry1 = Nothing
    Set objTry2 = Nothing
    ...etc
    into both Workbook_BeforeClose and Workbook_BeforeSave. It doesn't work though, and at the moment I've resorted to physically deleting the buttons when I've finished playing. Can this be automated? By the way, these generated buttons seem to be a different sort of object to the sort you create by dragging the control from the toolbox. With the latter you can right click them and change their properties. With the generated buttons right click only allows you to 'edit' them.

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

    Set objTry1 = Nothing

    only releases the memory occupied by the variable objTry1; it doesn't do anything with the button itself. To delete it, use

    objTry1.Delete

    Or generate the buttons only once (not in the Workbook_Open event procedure) since they will remain in the workbook.

  13. #13
    Lounger
    Join Date
    Oct 2007
    Location
    Wirral, UK
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794612' date='23-Sep-2009 21:51']The instruction

    Set objTry1 = Nothing

    only releases the memory occupied by the variable objTry1; it doesn't do anything with the button itself. To delete it, use

    objTry1.Delete

    Or generate the buttons only once (not in the Workbook_Open event procedure) since they will remain in the workbook.[/quote]

    The one thing I've learned from this is I don't understand objects. So the button has an existence apart from its own object? I thought objTry1 was the button. Is the button like the body, with objTry1 being its soul? Too deep for me.

    Anyway, philosophy apart, I'm not getting anywhere. I tried objTry1.Delete first (in Workbook_Save) but it returned error 91 ('Object variable or With block variable not set'). so I went for plan B, moving the code out of Workbook_Open. I transferred it into Sheet1_Activate. Two problems here. Firstly; Sheet1_Activate is not executed when you open the workbook. You have to click on another sheet then click back to Sheet1 to create the buttons. Secondly; if I exit from the spreadsheet and this point and reopen it, the new buttons are still there. But I know Sheet1_Activate hasn't been re-executed, so the buttons are presumably persisting just as they were when I had the code in Workbook_Open.

    Not sure where to go from here.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Why exactly do you want to delete the buttons?

  15. #15
    Lounger
    Join Date
    Oct 2007
    Location
    Wirral, UK
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794921' date='25-Sep-2009 19:55']Why exactly do you want to delete the buttons?[/quote]

    If there's a way of creating them, via code, which doesn't involve the user having to do anything, then I don't need to delete them (unless VBA behaves as it did when I had the code in Workbook_Open and creates multiple buttons every time the spreadsheet is opened). As I said, I hoped the button-creation code would execute on its own from Sheet1_Activate but that event only executes when the user has done something. I want the buttons to appear on the form when a new user opens the spreadsheet for the first time

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
  •