Posts by Egad

Leaving this weekend. :bike: This is actually shorter than my annual mexican in-laws trip.
www.mapquest.com

http://www.a-communication.com/ubb/Forum7/HTML/002041.html

:drum:

cool.

This approach works for me on 800x600 but I haven't tested on other PCs & other resolutions.

Private Sub ResizeUserform()
Load UserForm1
UserForm1.Width = Application.Width * 0.8
UserForm1.Height = Application.Height * 0.8
UserForm1.StartUpPosition = 2 '(center on screen)
UserForm1.Show
End Sub

I like M.'s solution.
But it is surprising that Excel does not have a built in column number to letter function.

Or, better yet:

=SUBSTITUTE(Sheet1!$A$1&CELL("address",A1),"$","")

If you have no more than 26 columns you could use this in sheet2!a1 then copy over your range:

=Sheet1!$A$1&MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",COLUMN(A1),1)&ROW(A1)

The keypress command does not work on TAB. You could try this but its probably not quite what you are looking for.
Place this code in the sheet1 module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.OnKey "{TAB}", "SeeVal"
End Sub

..and this code in module1:

Sub SeeVal()
Cells(ActiveCell.Row, ActiveCell.Column + 1).Select
MsgBox ActiveCell.Value
End Sub

This wiil make the cursor TAB right and display a msgbox with the cell value.
The downside is that using OnKey disables & over-rides the regular TAB behaviour.

Thank you for the VBA solution Andy.
I hadn't run into non-breaking spaces before.

Aha!
=TRIM(SUBSTITUTE(D5,CHAR(160),""))

I am copy-pasting data off the internet which comes into Excel with leading spaces. eg: " COMMUNICATIONS".

I cannot use =TRIM to remove the spaces as they are Char(160) 'non-breaking spaces' not the usual char(32) spaces.
I can't even use trim in VBA code to remove the spaces.

Any suggestions? I have attached a worksheet.

The cheelo.nl software above claims to work 99% with the Analysis Toolpak.

Never mind. I didn't see your "additional info from prior post" thread.
Bad. Bad Budman. :poke:

Background colours can be changed with conditional formatting (click on Format-Conditional Formatting).
You mention an IF covering multiple columns (sounds like a case for =SumProduct) but you have not given enough info about which columns or how the data is organized for us to give a specific solution.

PS: Bad. Bad Mr Excel. I always suspected....

XL-Dennis has a translator:
http://www.ozgrid.com/forum/viewthread.php?tid=3185

..and there is also this one for formulas & macros:
http://members.chello.nl/keepitcool/addins.html

Here is a basic freebie that I haven't tried yet. http://www.web100.com/sib/excel-to-html.html

Ok Reese, I made some minor modifications to the code above and it does work.

1) Open the Visual Basic Editor (Tools-Macro-Visual Basic Editor)
2) Find & click on your VBAProject (your file name)
3) Copy the sub SaveMacro code to a module (ie Module1) within your project - if you don't see a module then you can create one by clicking on Insert-Module.
4) change the aReg= and aDir= lines in the SaveMacro code to be your file name prefix and your save-directory location.
5) If you go back to your spreadsheet you can invoke the macro by clicking on Tools-Macro-Macros-SaveMacro-Run
(it should work - crosses fingers :))
6) you can make an "ALT-a" short-cut by clicking on Tools-Macro-Macros-SaveMacro-Options then enter the "A" or "a" - remember -it is case sensitive.
Good luck. Let me know how it turns out.

Yup, like this:

VarThisFileName = ActiveWorkbook.Name
VarOtherPath = "y:\whatever\"
VarOtherFileName = "hello.xls"
VarOtherFullFileName = VarOtherPath & VarOtherFileName

Workbooks.Open Filename:=VarOtherFullFileName
'do stuff
Windows(VarOtherFileName).Close SaveChanges:=True
Windows(VarThisFileName).Activate

Oh, I had it backwards.
I don't believe you can make changes to a workbook (change cell values) without opening, changing then saving it.

Below is modified from code that I use. It checks a directory for the next available number. If file xxx0001 and xxx0002 exist then it will save the existing file as xxx0003. (I haven't checked this mod for bugs). This allows multiple users to create sequenced filenames in the same directory.

Sub SaveMacro()

aReg = "Cancellation Filing RR "
aDir = "y:\whatever\"
aRegLen = Len(aReg) + 1

mLargest = 0
folderspec = aDir

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 In fc
s = s & f1.Name
s = s & vbCrLf
If Right(f1.Name, 4) = ".xls" Then
m = Val(Mid(f1.Name, aRegLen, 4))
If m > mLargest Then
mLargest = m
End If
End If
Next

mLargest = mLargest + 1
L = Len(mLargest)
Select Case L
Case 1
aFile = "000" & mLargest & ".xls"
Case 2
aFile = "00" & mLargest & ".xls"
Case 3
aFile = "0" & mLargest & ".xls"
Case 4
aFile = mLargest & ".xls"
End Select

aSaveAs = aDir & aReg & aFile

Msg = "File will be saved as:" & Chr(13) _
& aSaveAs & Chr(13) & Chr(13) & "If this is incorrect then Cancel"
Style = vbOKCancel + vbQuestion
Title = "File to be saved and numbered via automatic sequencing"
response = MsgBox(Msg, Style, Title)

If response = vbOK Then
ActiveWorkbook.SaveAs Filename:=aSaveAs
Else
End
End If
End Sub