Posts by KjBox

**KjBox** · Jan 15th 2007

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

**KjBox** · Jan 15th 2007

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.

Code

Private Sub AddResults()


    Dim iRow As Integer
    Dim iCol As Integer
    Dim sWinner As String
    Dim sLoser As String
    sWinner = Me.listWin.Value
    sLoser = Me.listLose.Value
    If sWinner = sLoser Then
        MsgBox "You have entered the same name for winner and loser. Please reselect", , _
        "Winner and Loser Identical", vbCritical
        Sheet7.listLose.Value = ""
        Sheet7.listWin.Value = ""
        Exit Sub
    End If
    iRow = Application.WorksheetFunction _
 [B]   .Match(sWinner, Sheet1.Range("D8:D107"), 0)[/B]
    iCol = Application.WorksheetFunction _
    .Match(sLoser, Sheet1.Range("C6:CZ6"), 0) - 2
    If Sheet1.Range("D7").Offset(iRow, iCol) <> "" Then
        MsgBox "A match between these two players has already been posted", , _
        "Duplicated Match", vbCritical
        Sheet7.listLose.Value = ""
        Sheet7.listWin.Value = ""
        Exit Sub
    End If
    Sheet1.Range("D7").Offset(iRow, iCol).Value = "W"
    Sheet1.Range("D7").Offset(iCol + 1, iRow - 1).Value = "L"
    Sheet7.listLose.Value = ""
    Sheet7.listWin.Value = ""
    Sheet7.listWin.Activate
    
End Sub

Display More

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

**KjBox** · Jan 15th 2007

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

**KjBox** · Jan 15th 2007

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

**KjBox** · Jan 15th 2007

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

**KjBox** · Jan 15th 2007

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)

Code

Sub AddResults()
    Dim iRow As Integer
    Dim iCol As Integer
    Dim sWinner As String
    Dim sLoser As String


    sWinner = Range("H28").Text
    sLoser = Range("H30").Text
    iRow = Application.WorksheetFunction _
    .Match(sWinner, Sheet1.Range("D8:D107"), 0)
    iCol = Application.WorksheetFunction _
    .Match(sLoser, Sheet1.Range("E6:CZ6"), 0)


    If Sheet1.Range("D7").Offset(iRow, iCol) <> "" Then
    
    MsgBox "A match between these two players has already been posted", , _
    "Duplicated Match"
    
    Exit Sub
    End If
    
    Sheet1.Range("D7").Offset(iRow, iCol).Value = "W"
    Sheet1.Range("D7").Offset(iCol, iRow).Value = "L"
    
    Sheet7.Range("H28").Select
    
End Sub

Display More

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

**KjBox** · Jan 14th 2007

Re: Starting Spreadsheet Opens Information Message

Try This:

Code

Private Sub Workbook_Open()


 MsgBox "This spreadsheet can design both single-leaf and cavity walls." _
            & Chr(13) & Chr(13) & "If only single-leaf wall is to be designed," _
            & Chr(13) & "deselect cavity wall option and complete only outer leaf design sheet." _
            & Chr(13) & Chr(13) & "If cavity wall is desired, select the cavity wall option and complete both input sheets.", , _
            "Panel Wall Design:"


End Sub

Display More

**KjBox** · Jan 13th 2007

Re: Worksheet Protection With Password

Thanks Roy, of course that is the way to go, one of these days I will wake up!!

I already have the the data etry cells unlocked.

Charlie

**KjBox** · Jan 13th 2007

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?

**KjBox** · Jan 13th 2007

Re: Worksheet Protection With Password

Hi Roy,

Thanks for that advice, it is exactly what I want.

Where should I place this? At the start of each module or in the workbook, or in each worksheet?

Charlie

**KjBox** · Jan 13th 2007

Re: Worksheet Protection With Password

Ok, realised what I was doing wrong! all well now thanks a lot.

**KjBox** · Jan 13th 2007

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

**KjBox** · Jan 13th 2007

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

**KjBox** · Jan 12th 2007

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

**KjBox** · Jan 12th 2007

Re: Selecting Cell At Row/column Intersect

Hi Bill,

YESSSSS it works a treat, thank you so much for your help.

: D : D : D

Charlie

**KjBox** · Jan 12th 2007

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

**KjBox** · Jan 12th 2007

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

**KjBox** · Jan 12th 2007

Re: Selecting Cell At Row/column Intersect

Hi Bill and KJ,

Thanks for your replies. Yes I was trying to run the macro whilst on sheet1 not sheet2 DUHHH!!!!

Sorry to doubt your brilliance, Bill

Thanks again folks.

Charlie

**KjBox** · Jan 12th 2007

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

**KjBox** · Jan 12th 2007

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.