Re: Conditional Format Via Code For Entire Workbook
The code in question is only triggered by a change on in the workbook and only affects the cell that initiated the change.
Re: Conditional Format Via Code For Entire Workbook
The code in question is only triggered by a change on in the workbook and only affects the cell that initiated the change.
Re: Vba Conditional Format Entire Workbook
The code you have at the moment is in the change event for a particular worksheet.
You need to place the same code in the
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
sub routine of the ThisWorkbook module.
In the VBA editor open the module 'ThisWorkbook', this should be just under the module for the last worksheet in your workbook. In the dropdown menus just above the code window choose Workbook in the left and SheetChange in the right, then paste the code within the created subroutine.
Re: VBA? Tack on extra characters after entering a value
Just a word of warning for the custom format method:
This will not change the underlying value of the cell. If you enter 20 in A1 the custom format will display '20-44' but a formula in B1 =A1+20 will return 40. (this will actually show '40-44' unless you have applied a format to B1).
Also this will not work for formulas such as VLOOKUP. If you look for =VLOOKUP("20-44",A1:A9,1,0) you will get an error as the underlying value is only 20.
Weasel
Re: Array of Worksheet Names
Sorry, go caught up at work...
No you dont need to select to do most things however some things do need to be active to set properties:
Sub arraytest()
arrSheets = Array("Sheet1", "Sheet2", "Sheet3")
For i = LBound(arrSheets) To UBound(arrSheets)
With Sheets(arrSheets(i))
.Cells.Interior.ColorIndex = xlNone
.Cells.FormatConditions.Delete
.Activate
ActiveWindow.DisplayHeadings = True
End With
Next i
End Sub
Display More
NB:
1) I mentioned before the methods you were attempting were for cell ranges hence the .Cells qualifier
2) In ordr to set the DisplayHeadings setting I activated each window as I looped through the array - cant promise its the best way but it works with little to no performance drop...
3) There is no humour in arrays!
Cheers
Weasel
Re: My daddy always said I'd go array...
It would appear that you want the array to contain the names of sheets but it contains a mixture of possible names and ranges....
The methods you are executing are for ranges not for the sheet object. Can you describe what you are trying to acheive and give some parameters?
Cheers
Weasel
Re: Saving Cell Address by selecting It
OK - as far as i can tell....
By changing xl files via the taskbar you are leaving one instance of the application and entering another - the macro doesnt like this.
You can prevent this from being an option by:
BUT (big BUT, like mine) it's rarely a good thing to mess about with a users interface. if you do, be sure to restore the users original setting.
Regards
Weasel
Re: Saving Cell Address by selecting It
Intersting that it doesnt work that way!
I would not recommend rolling this out to other users with such a fine window of success. Will try to look at this some more to make it a little more bullet proof.
Regards
Weasel
Re: Sumproduct Using Range Names
I'm afraid I'm having trouble visualising both what you have done and what you are trying to achieve.....
Could you post an example?
Re: VLOOKUP and Data Validation
OK - Its been a long day, I'm tired and I've had a couple of Ales (from near where you are Dave - Little Creatures - second best Australian beer ever)
It sounds like the VLOOKUP is supposed to return the name of your named range?
If so try using:
=INDIRECT(VLOOKUP(I2,$E$55:$F$65,2,FALSE))
Regards
Weasel
Re: Removing everything to the left of a character in a cell
ahso!
remove left of >
keep left of >
close......
Re: Saving Cell Address by selecting It
Worked for me - maybe I just got lucky....
I ran the macro from the book that contained the code. When the input box popped up I selected Window - and the file I wanted, then clicked on a cell.
What are you doing when the code is not acheiving the desired result?
Re: Complete Cell entry
This code will pause the execution of the macro while the user enters a value but will carry on even if the users Cancels out of the inputbox.
To force an answer try:
Do While Range("E8").Value = ""
stfn = InputBox("Please enter a Filename")
Range("E8") = stfn
Loop
Regards
Weasel
\
Re: Saving Cell Address by selecting It
Hmmm - nice Rich! Like it...
But AbuYehya, that isnt what you asked for the first time at all... Please try to specify as accurately as you can, what it is you need or time may be wasted generating useless solutions.
What Rich has cunningly suggested will work with a small addition:
Re: Removing everything to the left of a character in a cell
Or
=LEFT(A1,FIND(">",A1,1)-1)
Re: Saving Cell Address by selecting It
Try placing this code in the module of the sheet that will be used to select the target cell:
Dim SelectOn As Boolean
Dim myCell As String
Sub getCell()
SelectOn = True
MsgBox "Please select cell"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If SelectOn = True Then
myCell = Target.Address
Sheets("Sheet1").Range("a1") = myCell
SelectOn = False
End If
End Sub
Display More
For good measure I prefer to set any variable such as this to False when the workbook opens to be sure there are no problems. In the ThisWorkbook module:
Regards
Weasel
Re: Query on VBA
Possibly this might be easier done without VBA. In the attached workbook I created 3 named ranges on sheet1 named Mumbai, Chennai & Bangalore.
On sheet two I set up data validation (under the Data menu) to restrict the contents of B1 to a list that is populated by the named ranged entered in A1 [ =indirect($a$1) ]
See if this works for you.
Regards
Weasel
Re: Your opinion on searching a CSV file
No, not at all. I am a relative new comer to VBA (or any type of code) and have never come across the syntax before.
If most of the confusion occurs with street suffixs you could make the suffix a seperate entry using a list of all official abbreviations ie. CR, ST, PDE, CCT, RD and then concatenate the two entries as your search string.
Cant help you with the code as I dont understand it :O)
Cheers
Weasel
Re: Your opinion on searching a CSV file
William,
What version of VBA are you using? This code looks old.....
Regards
Weasel
Re: Extract capitals from text
Kinda hard to qualify that kind of fuzzy logic into code:
Find capitals so long as they're not PO unless of course were talking about POTTS POINT, PORTSEA etc..
Or find the left most 3 words unless the suburb is 2 words like MANLY WEST
Or look for everything after rd, cr, st etc... unless there isnt one like 11 Tuna MANLY WEST
Not sure you'll have much luck with an algorith to do this....
You may have to try something like text to columns to seperate all the data and then work with it from there...
Cheers
Weasel