Re: Deleting a set number of characters in a cell
Just do something like this:
Re: Deleting a set number of characters in a cell
Just do something like this:
Re: Prevent Delete prompts on worksheets
use the following:
Application.DisplayAlerts = false
Then re-enable it once you are done:
Application.DisplayAlert = true
Re: Reading individual characters
If you are doing this in VBA, you can use the Left() and Right() functions
Left(MyString, 1) 'Will return the first char
Right(Left(MyString, 2), 1) 'Will return the second char
Right(Left(MyString, 3), 2) 'Will return the third
Right(Left(MyString, 4), 3) 'Will return the fourth
And so on..
So, if you want to loop this:
Dim I as Integer
For I = 0 to MyString.Len
MsgBox(Right(Left(MyString, I + 1), I))
This feature, (as far as i know), is to prevent malicious macros to run on your PC and cannot be removed programatically.
Although, it is possible to remove the prompt on a computer manually by doing the following:
Tools > Macros > Security
then, select LOW security
This will remove the prompt.
All this time, I could have used the * wildcard in my strings?
Thanks for the tip Jim. I had no idea.
Are there any other wildcards, such as the "?"
Best thing to do in this situation is somethign like this:
(Assuming PO-Box is entered in column A)
You can also use regular expressions for this, although since there are so many ways to write POBOX, your expression could get quite complicated.
If your interested in doing it with regular expressions, here is a good read:
When a user enters a comment in a cell, and you would like to flag that cell in a different color to make it stand out as a commented cell, here's how.
I initially wrote this because users were having trouble distinguishing which cells had comments. When a user enters a comment (RightClick > Insert Comment) in a cell, it will highlight the cell in a light shade of green to make it stand out.
The only thing I didn't include is a way to remove the highlight if a comment is deleted. Although, that could be easily implemented.
This code should be pasted in the "ThisWorkbook" object in your VBA editor:
Public PrevCommentCount As Integer Public CurrCommentCount As Integer Dim CWSName As String Dim PWSName As String '***When workbook opens, save comment count Private Sub Workbook_Activate() CurrCommentCount = ActiveSheet.Comments.Count End Sub '***When workbook is open, init variables Private Sub Workbook_Open() 'Ensure comments display indicator Application.DisplayCommentIndicator = xlCommentIndicatorOnly 'Get current comment count CurrCommentCount = ActiveSheet.Comments.Count End Sub '***Event fires when sheets are activated Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Flag = False Then CWSName = Sh.Name CurrCommentCount = Sh.Comments.Count End If End Sub '***Event is fired when sheets are deactivated Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) PWSName = Sh.Name PrevCommentCount = Sh.Comments.Count If PrevCommentCount <> CurrCommentCount And PWSName = CWSName Then For Each mycomment In Worksheets(Sh.Name).Comments Worksheets(ChartSheet).Range(mycomment.Parent.Address).Interior.ColorIndex = 35 Worksheets(ChartSheet).Range(mycomment.Parent.Address).Font.ColorIndex = 1 Worksheets(ChartSheet).Range(mycomment.Parent.Address).Font.Bold = True Next mycomment End If End Sub '***When new sheet is selected, EVENT fires Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Comments.Count <> CurrCommentCount Then 'Color all comments green For Each mycomment In Worksheets(Sh.Name).Comments Worksheets(Sh.Name).Range(mycomment.Parent.Address).Interior.ColorIndex = 35 Worksheets(Sh.Name).Range(mycomment.Parent.Address).Font.ColorIndex = 1 Worksheets(Sh.Name).Range(mycomment.Parent.Address).Font.Bold = True Next mycomment CurrCommentCount = Sh.Comments.Count End If End Sub
This code snippet will allow you to save all your existing charts into a GIF format. It will save them in a folder called /Charts located in the ThisWorkbook.Path directory.
This can be useful if you need to code an Index for your charts, or you want to output a Chart Report sheet in HTML or whatever. I found it extremely useful after I had finished implementing it, and I hope others will to.
In order for the following code to work, you will need to add a reference to "Microsoft Scripting Runtime" in your Tools > References (VBA editor). So without further delay, here is the code:
'Sub to save all existing charts to file Sub SaveAllCharts() For Each ws In Worksheets ws.Activate If ActiveSheet.ChartObjects.Count <> 0 Then For I = 1 To ActiveSheet.ChartObjects.Count Call SaveChartAsGIF(ws.Name, I) Next I End If Next ws End Sub '***Function will save the chart in gif format Function SaveChartAsGIF(SheetName, ChartID) 'Temp dimensions to save current chart size and position Dim H As Double Dim W As Double Dim T As Double Dim L As Double 'FileName variable, and FSO Dim FName As String Dim RepString As String Dim mFileSysObj As New FileSystemObject 'select chart Worksheets(SheetName).Activate ActiveSheet.ChartObjects(ChartID).Activate ActiveChart.ChartArea.Select 'Set chart object in a variable Dim ChtOb As ChartObject Set ChtOb = ActiveChart.Parent 'Save current dimensions and position H = ChtOb.Height W = ChtOb.Width T = ChtOb.Top L = ChtOb.Left 'Set printable size ChtOb.Height = 288 ChtOb.Width = 500 ChtOb.Top = 144 ChtOb.Left = 182.25 'create Charts directory FName = ThisWorkbook.Path & "\Charts\" & ActiveChart.Name & ".gif" For I = 1 To 99 RepString = " Chart " & CStr(I) FName = Replace(FName, RepString, "") Next I If mFileSysObj.FolderExists(ThisWorkbook.Path & "\Charts\") = False Then Call mFileSysObj.CreateFolder(ThisWorkbook.Path & "\Charts\") End If 'Save the Chart to a GIF format ActiveChart.Export Filename:=FName, FilterName:="GIF" 'Restore original dimensions ChtOb.Height = H ChtOb.Width = W ChtOb.Top = T ChtOb.Left = L End Function
If anyone is interested, I can also post my code that displays all these GIF charts into a web browser. Enjoy.
Okay, first, in order to generate an incremented number, you need to find out what number you are currently using. To do this, I suggest using the "ThisWorkbook" object. In the "ThisWorkbook" object, paste the following code (or something like this):
Public Number as Integer Private Sub Workbook_Open() Dim CurrentNumber as Integer Dim TempStr as String 'Get current number out of workbook name 'The following code fragment is crude, and untested (and is missing error control)... You may want to revise this when you use it TempStr = Left(ThisWorkBook.Name, Len(ThisWorkBook.Name) - 5) TempStr = Left(TempStr, 1) CurrentNumber = CInt(TempStr) 'Increment the current number CurrentNumber = CurrentNumber + 1 'Save it in Public variable Number = CurrentNumber end sub
Okay, that takes care of the number. Now to save your new format you can paste this in any module:
Sub savings() 'Get current date, and format it Dim TempDate as string TempDate = Date TempDate = Replace(TempDate, "/", "_") 'Save Application.DisplayAlerts = False ActiveSheet.copy ActiveWorkbook.SaveAs Filename:="C:\SCH\" & TempDate & " " & ThisWorkbook.Number & ".xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close End Sub
By default, your labels should line up with the tick marks.
What kind of chart are you using?
Ok, here is my problem.
When a cell is labelled as 24_MAY_04 or something similar, excel does not reckognize it as a DATE FORMAT. even if the Cell format is set to a DATE, it is still not a DATE FORMAT.
If by chance a user enters this type of format in a cell, my macros will crash since the dates are incorrect. It crashes because I use the Data in a chart, and when it hits these values, Excel does not reckognize it as a date format.
23_May_04 is not a date in excel
I'm wondering how would you check to see if a cell contains a date format?
is there a function similar to IsNumeric for dates?
I figure, if it is a date, it would be automatically formatted as a DATE FORMAT.
My main concern is, I need to check if a cell is reckognized as a DATE in excel.
it would be....
you should be able to use the TextBox.Value property to get the text.
Worksheets("Sheet 1").Range("A1").Value = TextBox.Value
my initial question was quite vague, sorry to have wasted your time.
Also, thank you for answering my question
There is no swap function "built-in" excel.
What you could do is get the user to specify what time zone he is in, and then adjust the rest of the value accordingly.
The time zone the user will specify will be your comparison points.
This is just a suggestion.
What I meant to ask was, is there a SWAP function?
lets say we have data in A and B
to swap data, i need to:
- Copy A to a temp
- Copy B to A
- Copy Temp to B
- Destroy Temp
I was just wondering if there was a pre-made function available. Although while waiting for my answer, I actually wrote my own, so its all good