More Excel Notes

I realized that I haven't posed since November. I'm not doing as well as I had planned. I'm also in the final stages of releasing a global reporting tool for my current client so I've been spending a lot of time getting things polished and working with the test team. However, I do have some new things to add. Simple stuff, but really helpful.

The tool I have been working on for the past two months uses a text box for a report name, that name is dropped into the Excel file as the tab name as well so I've had to do some checking of the name to make it tab compatible. Here is what to look for.

Excel only allows 31 characters for a tab name. This can easily be limited in the "MaxLenght" property of the text box. However, based on how you are using this users may need some additional information. In the tool I built I added a simple counter at the end of the text box that shows the user the current number of characters against the max allowed i.e. "0/31". Then used the text boxes "Change" event to update the first number based on the user input with this code:

charAvailable.Caption = CStr(Len(reportName.Text)) & "/31"

Another thing to check is any invalid tab characters in the text box. Excel does not allow :, \, /, ?, *, [, or ] to be in a tab name. There may be an easier way to do this but I simply use the Replace function to replace an invalid character with whatever other acceptable character I want (in most cases either an empty string "" or a space " "). Here is the code:

RptName = txtRptName.Text

RptName = Replace(RptName, ":", " ")

{same code as above for remaining invalid characters}

txtRptName.Text = RptName

Again, there may be a simpler way, but this way works and it's relatively fast since there is a max of 31 characters for the string.

Two more checks that I run are checking for blanks and checking for completely numeric names. Both of these are done via if statements. Here is the code:

If txtRptName = "" Then

End If

If isNumeric(txtRptName) Then

End If

I built a function called UserForm_Validation that returns True or False where all of this code runs, each section will exit the function with a setting of False when a validation error is found. The call on the main source line will exit the code completely if a value of "False" is returned.