Posts by Barryj

Re: VBE Color Pallet

Thanks dave I will give it a go.

Re: VBE Color Pallet

I was just wondering if there was a way of using the regular pallet that is available in Excel when building a userform, or an addin that was available for this purpose?

Does anyone know how to show the full colour pallett in the VBE toolbox so that I can have different colours than the standard provided for textboxes and lables.

Re: Update Golf Handicaps According To Rules

Ok, in cell D6 if you type in 72 then the handicapps will be adjusted for stroke event, if you type in 36 then you are playing stableford.

Type the the players name in the cells in column A & F the put the handicapp in say 25 in column B & C and G & H, then the score for that round in column D & I if it is greater than cell D6 then it will give .1, if it is less it will take the appropiate handicapp away according to the table on the top left, this works for up to 36 handicapp, it can be adjusted if you want it to be less.

Hope this helps explain it a little.

Re: Update Golf Handicaps According To Rules

Try this if it works for you, it adjusts handicapps when score is put in cell D or I, it works with both stroke and stableford, just put their handicapp in cell B & C and when their next score is added it will adjust the actual handicapp and either increase or decrease depending on their score.

Re: Left & Right Formula To Show Number Not/as Well

Thanks guys for the help on this, problem now solved, thanks Filo65 your formula worked fine, thanks again for the help.

Re: Left & Right Formula To Show Number Not/as Well

Filo65, there is nothing showing from your post?

Re: Left & Right Formula To Show Number Not/as Well

I tried both of Cringes formulas, they work for double digit numbers but not for a single digit.

Re: Protect Cells From Deletion

Can you add some code to unprotect while macro is running and then protect when macro is finished running.

Re: Left & Right Formula To Show Number Not/as Well

The file displays the formulas that I had, when C15 is less than 18 it should display the number to the left and over 18 the number to the right.

As you can see from the attachment some cells only contain single digit numbers.

Thanks for the help.

Re: Left & Right Formula To Show Number Not/as Well

Still only showing false for single digit from 1/25 number if C15 is under 18 and 2 if C15 is greater than 18?

Re: Left & Right Formula To Show Number Not/as Well

Thanks for the replys guys, but I am getting a false result when C15 is greater than 18 and I am only getting 1 number when C15 is under 18 ie: 1/25 is returning only 2 not 25.

I have numbers in a cell such as 1/25, 4/31, 12/35 etc I have the current formula to extract the left or right number but when there is only a single digit it also includes the / is there a way of eliminating this.

The numbers vary from 1 to 2 digits.

My current formula is =IF(C15<18,LEFT(E8,2))+IF(C15>18,RIGHT(E8,2))

I only want it to show the number not the forward slash

Thanks Barry.

Re: Equals Left, Formula

Thanks for the help guys this has solved my problem, Thanks.

Re: Protect Individual Cells?

Lock the cells you need to and leave the others as is then password protect the sheet, this will leave the other cells unlocked and the ones you require locked protected.

I have a list of names, first and last that I want to read the first names only, I have tried the =LEFT but the names vary in lenght so that doesnt really work.

John Gottas-Healy
Richard Montgomery
Brian Reeves
Stephen Hewitt

I only want the first name from each, is this possible some other way?

Re: Macro To Unhide Selected Sheet Then Hide When Another Sheet Selected

Thanks Roy will look at this option.

I have an index page which is linked to approximatley 30 sheet, these sheets are hidden.

I also have 30 sheets visable along the bottom of the work book.

I want to be able to select a sheet from the index page which will then unhide the selected sheet, but when I click on another sheet I want that sheet to again become hidden.

Is this possible using VBA?

Thanks for any help or advice.

Re: Sort By More Than 3 Columns

If you want to use a macro this will sort 5 columns for you when the sheet is activated.

Private Sub Worksheet_Activate() 
Range("A2", Range("F65536").End(xlUp).Address).Select 
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending _ 
, Key2:=Range("C2"), Order2:=xlDescending _ 
, Key3:=Range("D2"), Order3:=xlDescending 


Selection.Sort Key1:=Range("E2"), Order1:=xlDescending _ 
, Key2:=Range("F2"), Order2:=xlDescending _ 


End Sub

Hope this helps

Re: Formula: Last x Characters, Text or Numeric

Thanks for the info Dave, but got it sorted with The Cringes formula.