Forum Home
Press F1
Thread ID: 53914	2005-01-31 02:27:00	Excel Macro/VBA help needed	Tony (4941)	Press F1

Post ID	Timestamp	Content	User
319623	2005-01-31 17:57:00	=CONCATENATE(A1," ",B1) where a1 is the first name and b1 is the last name and the formula could be entered in c1 and copied down the column this will allow you to delete entries in a1 and b1 and with no errors showing in c1 only what appears to be a blank cell I'm not sure how that differs in usefulness from the original suggestions, except that you don't get an error in Col C when you delete A & B. You still need to do the paste special if you want to set col C in concrete. Thanks for the suggestion though - it just shows there are many ways to skin a cat.	Tony (4941)
319624	2005-01-31 18:01:00	Hi Sparky, yes you can sort sheets using code. Capt Jimbo, yes a macro is the only way but why would you say this is overkill? There are 40 sheets afterall. Follow these steps to add the macro 1. Open the Visual Basic Editor (ALT+F11 or Tools|Macro|Visual Basic Editor from the menu) 2. Select Insert|Module from the menu 3. Paste the code below in the right-hand window 4. Close the Visual Basic Editor (ALT+Q or File|Close and return to Microsoft Excel from the menu) Sub SortSheets() 'Code courtesy of John Walkenbach 'This routine sorts the sheets of the 'active workbook in ascending order. Dim SheetNames() As String Dim SheetHidden() As Boolean Dim i As Integer Dim SheetCount As Integer Dim VisibleWins As Integer Dim Item As Object Dim OldActive As Object If ActiveWorkbook Is Nothing Then Exit Sub ' No active Workbook SheetCount = ActiveWorkbook.Sheets.Count ' Check for protected workbook structure If ActiveWorkbook.ProtectStructure Then MsgBox ActiveWorkbook.Name & "is protected.", _ vbCritical, "Cannot Sort Sheets." Exit Sub End If ' Disable Ctrl+Break Application.EnableCancelKey = xlDisabled ' Get the number of sheets SheetCount = ActiveWorkbook.Sheets.Count ' Redimension the arrays ReDim SheetNames(1 To SheetCount) ReDim SheetHidden(1 To SheetCount) ' Store a reference to the active sheet Set OldActive = ActiveSheet ' Fill array with sheet names For i = 1 To SheetCount SheetNames(i) = ActiveWorkbook.Sheets(i).Name Next i ' Fill array with hidden status of sheets For i = 1 To SheetCount SheetHidden(i) = Not ActiveWorkbook.Sheets(i).Visible ' unhide hidden sheets If SheetHidden(i) Then ActiveWorkbook.Sheets(i).Visible = True Next i ' Sort the array in ascending order Call BubbleSort(SheetNames) ' Turn off screen updating Application.ScreenUpdating = False ' Move the sheets For i = 1 To SheetCount ActiveWorkbook.Sheets(SheetNames(i)).Move _ Before:=ActiveWorkbook.Sheets(i) Next i ' Re-hide sheets For i = 1 To SheetCount If SheetHidden(i) Then ActiveWorkbook.Sheets(i).Visible = False Next i ' Reactivate the original active sheet OldActive.Activate End Sub Sub BubbleSort(List() As String) Dim First As Integer, Last As Integer Dim i As Integer, j As Integer Dim Temp As String First = LBound(List) Last = UBound(List) For i = First To Last - 1 For j = i + 1 To Last If List(i) > List(j) Then Temp = List(j) List(j) = List(i) List(i) = Temp End If Next j Next i End Sub Follow these steps to run the macro 1. Select Tools|Macro |Macros (or Alt+F8) 2. Select SortSheets from the list of macros (its probably already selected) and click the Run button. In regards to your 2nd problem what sort of drop down box are you talking about. A validation box, ComboBox from the forms toolbar, ComboBox from the Control Toolbox or Visual Basic toolbars or something else?	Parry (5696)
319625	2005-01-31 18:07:00	=CONCATENATE(A1, " " ,B1) where a1 is the first name and b1 is the last name and the formula could be entered in c1 and copied down the column this will allow you to delete entries in a1 and b1 and with no errors showing in c1 only what appears to be a blank cell Hi Beama. Just in case you werent aware, using the concatenate formula is exactly the same as using the & between values such as =A1 & " " & B1. regards Parry :-)	Parry (5696)
319626	2005-02-01 00:12:00	Thanks Parry, That Macro worked a treat. Have put it on all my workbooks now. Bloody marvellous. About question 2. The box I am looking to widen is the name box on the left of the formula bar. A lot of the names in there are too long for the existing width of the box. Hopefully you can be as much help on this as you were with the macro. Cheers.	*Sparky* (311)
319627	2005-02-01 01:11:00	Hi Sparky, Im glad its worked well for you. If you save the macro in your personal.xls workbook it will be available for all Excel workbooks. The 2nd question is a bit more tricky. What your talking about is the Name Box control and is a built-in control with Excel. Unfortunately you cannot normally change the attibutes of built-in controls so the answer is no your stuck with it as is. Sometimes built-in controls can be changed with API calls but it depends whether MS built in the ability to alter the control behind the scenes. I'll have a look in my books when I get home but Im guessing this is a no goer. I could build a control that replicates its functionality but frankly its not really worth the effort. I guess your other option is to truncate the names your using a bit. Not convenient sometimes as you often want to give them a descriptive name.	Parry (5696)
319628	2005-02-01 01:20:00	Parry, Dont go to any trouble with the name box. I was hoping it was something easy. You say to save the macro to my personal.xls workbook. Do I make a personal.xls workbook and use this as a template? Sorry for sounding simple. Thanks.	*Sparky* (311)
319629	2005-02-01 01:50:00	No problem . The Personal . xls file is created when you select Tools|Macro|Record New Macro and in the Store In box select Personal Macro Workbook . The file is usually saved in the xlstart folder wherever you installed office and will be a hidden file . Presuming youve never recorded a macro and saved it in the Personal Macro Workbook, the easiest method is to record a dummy macro (just press enter a few times then stop the macro) . Once recorded close the book and a message may come up asking whether you want to save the Personal macro just recorded -say yes . Open the book again and select Alt+F11 to get into the VB Editor then in the left hand window you will see an explorer tree type view . Expand the tree of the VBProject(Personal . xls) until you see Module1 and double click on it . In the right hand window will be the code you just recorded which can be deleted . This is where you paste in the code for sorting the sheets . regards Parry .	Parry (5696)
319630	2005-02-01 03:10:00	Marvellous Parry, How did you learn so much about excel? I'll have to remember you for future reference. Thanks for all your help.	*Sparky* (311)
319631	2005-02-01 03:12:00	Hi Beama. Just in case you werent aware, using the concatenate formula is exactly the same as using the & between values such as =A1 & " " & B1. regards Parry :-) Yes I see that the logic of two methods are the same, I missed that reply sorry. I use Concatenate because I find it easy to understand especially when nesting formulas.	beama (111)
319632	2005-02-01 05:29:00	Marvellous Parry, How did you learn so much about excel? I'll have to remember you for future reference. Thanks for all your help. My pleasure. I guess I know alot about Excel because Im inquisitive, Ive read a couple of books about it and I answer alot of questions. Im one of the MVP's on Mr Excel (www.mrexcel.com) so check it out if your having problems with Excel. The response time to questions is normally pretty good.	Parry (5696)
1 2 3