Posts by KjBox

    Re: Listbox For Data Entry


    Hi Bill,


    I will try but the spreadsheet is about 1mb and even if I just post the 2 relavant sheets it will still be too large. I will see if I can butcher it to get it down to an acceptable size.


    Charlie

    Re: Listbox For Data Entry


    Hi Bill,


    I must be missing something here.


    This is what I have done:


    I copy/pasted your code above to replace what I had.


    I changed the fill source for my 2 list boxes to D8:D107 on the Chart sheet (I had already named this range 'Names.}.


    This is the code I now have, it is in sheet 7 ("Admin") and I run it from a drawing box.



    I changed the code in the bold row from C8:C107 to D8:D107 because that is the range where the match needs to be found in my case. The second range for the match is E6:CZ6 but I did not change but I did not change your code as I saw you had added a -2 at the end of that line. Is that anything to do with row 7 being blank cells? Because, if so, then they will definitely be removed. I also added the second message box.


    When I run the macro I get the following:


    Compile error.
    Method or data method not found.


    and the .listWin of the line sWinner = Me.listWin.Value is highlighted.


    I have not run macros from a command button before. In case this was the problem I made a command button, from the control toolbox, on the Admin sheet but could not assign a macro to it, right clicking on it gave me a menu but assign macro was not there. Am I doing something wrong there too?


    Sorry to be such a pain.


    Charlie

    Re: Listbox For Data Entry


    Forget the earlier post about getting the 'duplicated match' message box. I realised what I was doing wrong and it is too embarassing to say what it was!!!


    Charlie

    Re: Listbox For Data Entry


    Hi Bill,


    Sorry our replies crossed.


    Maybe I did not explain properly. I am now using 2 List Boxes created from the control toolbox. The sort of list you used would not be appropriate as the actual list could have in excess of 100 names. I fill it from a named range (now named 'Names') on another sheet.


    I tried running your code but got a compile error for the 'Me' in Me.listWin.


    Charlie

    Re: Listbox For Data Entry


    I have tried changing

    Code
    sWinner = Range("H28").Text 
        sLoser = Range("H30").Text


    To


    Code
    sWinner = ListBox1.Text 
        sLoser = ListBox2.Text


    But all that does is return the error message no matter what winner/loser names are used. Same happens if I use .Value instead of .Text


    Any ideas?


    Charlie

    I got the code shown below from a reply to a previous post.


    I have now changed from merely entering the data in cells H28 and H30 to using 2 List Boxes.


    I am having trouble altering the code to suit this change.


    Also to include another message box to warn if both list boxes return the same value (i.e. winner and loser names are the same). Or is there a way to prevent this error happening, in other words, to remove the winners name from the dropdown list for the loser's list box (the list source for both dropdown lists is the same)



    The last line also needs to be changed so that the winner's list box selected ready for the next entry.


    I hope somebody can understand what I mean and point me in the right direction.


    Charlie

    Re: Starting Spreadsheet Opens Information Message


    Try This:


    Re: Worksheet Protection With Password


    Yes, but with selecting unlocked cells allowed.


    The workbook is a master copy for backgammon tournament that runs evry couple of months, and the first macro to create a new workbook and name it for the current session of the tournament. I would need the set protection macro to be in the master and carry forward to the new workbook. Would placing it in the Workbook_Open event enable this?

    Re: Worksheet Protection With Password


    Thanks Wigi.


    However I still do not know the code to protect with a password in the first place. I tried recording a macro whilst protecting the sheet with a password but this retirns the same code as protecting without a password.


    Charlie

    I have a workbook where I need to protect the sheets with a password.


    However, for certain macros contained in the workbook to run the sheets need to be unprotected. I know the code to unprotect the sheet but the password is required. I do not want other users to know this password.


    What I need is the code to protect a worksheet with a password. Then, when a macro runs, to unprotect without user having to enter the password and reprotect with the original password when the macro has completed its run.


    Please note: I am NOT looking for any form of password breaking code!!


    Thanks,


    Charlie

    Re: Selecting Cell At Row/column Intersect


    Hi Bill,


    Yes there is another row below the names in row 6 it has blank cells.


    I edited the last 2 lines of your code to: Sheet1.Range ("D7")..... and it works fine. I do intend to get rid of the row of blank cells, they were there for something else I was trying to do but will now abandon I think! Then, will re-edit code to read ...("D6")....


    The workbook also contains charts that represent the top 20 players with most matches played, most wins and best match:win ratio as a %. The last thing I need to do is work on a code that will set the scale of the charts (they are bar charts) to suit the number of matches/wins/% the top and the 20th best players and for the scale to change automatically as the tournament progresses.


    So expect another cry for help in the near future in another post!!!!!!


    Your help has been invaluable, thanks.


    Charlie

    Re: Selecting Cell At Row/column Intersect


    Hi Bill,


    Ok I mow know exactly where the winner/loser names will be entered.


    WINNER will be entered in cell H37 of sheet7 which is called "Admin"
    LOSER will be entered in cell H39 in the same sheet.


    These can be entered either by typing or using a dropdown list of all names.


    I will add a botton to update the grid after each entry and return to sheet7 clear cells H37 and H39 then select cell H37 ready for the next entry. So no need to involve a list of winners and losers, I see no advantage in doing so.


    The grid will be on sheet1, called "Chart" . The names are in column D from row 8 to row 107 and in row 6 from column C to column CZ.


    So I guess your first code would be the one to use with relavant ranges and sheet refrences edited.


    Thanks,


    Charlie

    Re: Selecting Cell At Row/column Intersect


    Bill,


    I have just read your last reply properly!


    Results are usually added individually but at times it would be easier to list a number of results before adding them to the grid.


    I would greatly appreciate knowing the code to allow this.


    Thanks yet again,


    Charlie

    Re: Selecting Cell At Row/column Intersect


    Hi Bill,


    Thanks for your reply. I pasted your code into the text.xls workbook but it gives the following error:


    Run-time error '1004'
    Unable to get the Match property of the WorksheetFunction class


    Any ideas?


    I will not necessarily be using sheet2 cells A1 A2 in the final workbook but I am sure I will be able to alter your code to suit the actual location (if not then I will use sheet2 A1 A2 !!)


    Thanks
    Charlie

    Hi,


    I need some help with the correct code for the following operation (I have attached a sample of the sort of worksheet involved). This is for recording match results in a backgammon tournament.


    The match results are posted on another sheet and the winner allocated the letter W and the loser allocated L (in the sample workbook attached these are on sheet2 cells B1 and B2 with the winners name in A1 and the losers in A2, i.e. Bill and Susan) I have the code for this working properly.


    What I need to do next is transfer the W and L onto the grid in sheet1 and insert the W in the cell that is the intersection between the row with the winners name and the column with the losers name. Then insert the L in the cell that is the intersection between the row with the losers name and column with the winners name. I hope that makes sense!


    If it is simpler to insert the W and L into the grid directly from the winner and loser names in sheet2 col A rather than allocating the W and L in col B first, then that is great.


    Thanks.