Posts by Simon Lloyd

Simon Lloyd · Oct 31st 2006

Re: Variable Not Assigning Correctly

Hi I put some test figures in two workbooks, ran your code and it didnt create an error, could you explain or provide the values you are using in a test book and attatch it?

Regards,
Simon

Simon Lloyd · Oct 31st 2006

Re: Variable Not Assigning Correctly

You need to post all of your code so we can see why it would have stopped at your message boxes, post it and you will be sure to get your answer.

Regards,
Simon

Simon Lloyd · Oct 31st 2006

Re: Save & Exit Workbook On Userform Unload Without Prompt

just add this line just before the line you posted, should do the trick for you

Code

ActiveWorkbook.Save

Regards,
Simon

Simon Lloyd · Oct 31st 2006

Re: Conditional Formatting For More Than 3 Conditions

In fact this will probably work a bit slicker for you, you need to paste it directly into the worksheet module you are working on, again the numbers of the colours aren't what you asked for but you will be able to get those as above.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyCell As Range
For Each oCell In Range("A1:C20")
Select Case oCell.Value
Case Is = "A+"
MyCell.Interior.ColorIndex = 8
Case Is = "A"
MyCell.Interior.ColorIndex = 8
Case Is = "A-"
MyCell.Interior.ColorIndex = 5
Case Is = "B+"
MyCell.Interior.ColorIndex = 5
Case Is = "B"
MyCell.Interior.ColorIndex = 3
Case Is = "B-"
MyCell.Interior.ColorIndex = 3
Case Is = "C+"
MyCell.Interior.ColorIndex = 6
Case Is = "C"
MyCell.Interior.ColorIndex = 6
Case Is = "C-"
MyCell.Interior.ColorIndex = 4
Case Is = "D"
MyCell.Interior.ColorIndex = 4
End Select
Next MyCell
End Sub

Display More

Regards,
Simon

Simon Lloyd · Oct 31st 2006

Re: Conditional Formatting For More Than 3 Conditions

Below is just one routine...Case Select could also be used.

Open Visual Basic Editor(ALT + F11) and select your workbook Project in
Project Explorer. Insert>Module. Copy/paste this into the module. Assign it
to a button or Run it from Tools>Macro>Macros, or you could put the body of the code into a worksheet change event so it will change everytime a cell is changed

Note, it is set up to work on column A only. Adapt column and number ranges
to suit. The colour values aren't the ones you asked for all you have to do is a record a macro whilst changing some cells to the colours you want and then look at the macro to get the colour indexes.

Code

Sub ColorValues()
Dim Lastrow As Long
Dim Values As Variant
Dim cell As Variant
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set Values = Range("A1:A" & Lastrow)
    For Each cell In Values
        If cell.Value = "A+" Or cell.Value = "A" Then
            cell.Interior.ColorIndex = 4
        ElseIf cell.Value = "A-" Or cell.Value = "B+" Then
            cell.Interior.ColorIndex = 3
        ElseIf cell.Value = "B" Or cell.Value = "B-" Then
            cell.Interior.ColorIndex = 5
        ElseIf cell.Value = "C+" Or cell.Value = "C" Then
            cell.Interior.ColorIndex = 6
        ElseIf cell.Value = "C-" Or cell.Value = "D" Then
            cell.Interior.ColorIndex = 15
        End If
    Next cell
End Sub

Display More

Regards,
Simon

Simon Lloyd · Oct 31st 2006

Re: Macro Finds And Copy All The Cells Above

This should do what you want, i had a small problem with it that i had to get help for but it works perfect now!

Code

Sub FindText()
    Dim T1
    Dim AR As Variant
    Dim i
    i = 1
    T1 = InputBox("Enter text you wish to find", "Text Finder")
    Cells.Find(What:=T1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    AR = ActiveCell.Row
    Rows("1:" & AR).Copy
    ''''''''Choose where its going to go here like, Sheets("Sheet1").Range("A1") or wherever
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Display More

Regards,
Simon

Simon Lloyd · Oct 25th 2006

Re: Sort Userform Textboxes

Andy, i didnt tke the credit or the mick!, it was as i was finishing working nights and didnt have the inclination to type a lot as i said in my first reply

Quote

Simon Lloyd I dont know how to do it, but i know that doesnt help.

The theory is, you create an array out of the 12 boxes and examine them to return your required value, the array would probably have to be transposed but you dont have to deliver the array to a sheet to examine it.

I'll have a look around for examples and post back!

Regards,
simon

Display More

I have never or will ever pass other peoples work off as my own!, on more occassions than i can remember i have admitted things are not within my capability!

My apologies if you were offended Andy!

Regards,
Simon

Simon Lloyd · Oct 25th 2006

Re: Sort Userform Textboxes

I was right heres the code for 12 boxes the array is hard coded to 12 as per your post!, put a button on your form and included this code for it.
Regards,
Simon

Code

Private Sub CommandButton1_Click() 
    Dim arr(12, 2) 
    i = 1 
    For Each cnt In Me.Controls 
        If TypeName(cnt) = "TextBox" Then 
            arr(i, 1) = cnt.name 
            arr(i, 2) = Val(cnt.Text) 
            i = i + 1 
        End If 
    Next cnt 
     
    holder = WorksheetFunction.Max(WorksheetFunction.Index(arr, 0, 3)) 
    For i = 1 To 12 
        If arr(i, 2) = holder Then 
            MsgBox arr(i, 1) & ", " & arr(i, 2) 
        End If 
    Next i 
     
End Sub

Display More

Simon Lloyd · Oct 25th 2006

Re: Sort Userform Textboxes

I dont know how to do it, but i know that doesnt help.

The theory is, you create an array out of the 12 boxes and examine them to return your required value, the array would probably have to be transposed but you dont have to deliver the array to a sheet to examine it.

I'll have a look around for examples and post back!

Regards,
simon

Simon Lloyd · Oct 25th 2006

Re: Delete Residual Code From Sheet After Sheets.copy

Glad you got sorted!

Regards,
Simon

Simon Lloyd · Oct 25th 2006

Re: Saving A Worksheet As A File

Just make sure you use my last post as it pastes just values and turns the copy mode off!

Regards,
Simon

Simon Lloyd · Oct 25th 2006

Re: Saving A Worksheet As A File

Sorry must be driving you mad this is my final amendment! I didnt read what you wanted properly!

Code

Sub SheetCopy() 
    Dim T1 
    n = ThisWorkbook.Sheets("Sheet1").Range("G5") ' Variable set for naming purposes
    T1 = InputBox("Enter Range To Be Saved" & Chr(13) & "Range must be typed in this fashion Ax:Axx", "Saving Range To New WorkBook") ''Inputbox to allow the user to save whatever range they like
    Sheets("sheet1").Range(T1).Copy '''Don't forget to change the source sheet name here
    Workbooks.Add ' This adds your new workbook
    Wn = ActiveWorkbook.Name ' 'variable set for ease later
    With Wn 
        Sheets("Sheet1").Select ' 'Select first sheet in new workbook
        Range("A1").Select 'select cell to start at
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False' Pastes values only to our selected loaction
    End With 
     
    ActiveWorkbook.SaveAs n & ".xls" 'Saves our workbook as the name we required
    Application.SheetsInNewWorkbook = 1 '''limits our new workbook to 1 sheet
     
    ActiveWorkbook.Close '''Closes the new workbook returns us to our original workbook
End Sub

Display More

Regards,
Simon

Simon Lloyd · Oct 25th 2006

Re: Delete Residual Code From Sheet After Sheets.copy

Everything you need is right here on Chip Pearsons page all well explained to!

http://www.cpearson.com/excel/vbe.htm

Regards,
Simon

Simon Lloyd · Oct 25th 2006

Re: Saving A Worksheet As A File

Just an amendment to remove the remaining two sheets in the new workbook

Code

Sub SheetCopy() 
    Dim T1 
    n = ThisWorkbook.Sheets("Sheet1").Range("G5") ' Variable set for naming purposes
    T1 = InputBox("Enter Range To Be Saved" & Chr(13) & "Range must be typed in this fashion Ax:Axx", "Saving Range To New WorkBook") ''Inputbox to allow the user to save whatever range they like
    Sheets("sheet1").Range(T1).Copy '''Don't forget to change the source sheet name here
    Workbooks.Add ' This adds your new workbook
    Wn = ActiveWorkbook.Name ' 'variable set for ease later
    With Wn 
        Sheets("Sheet1").Select ' 'Select first sheet in new workbook
        Range("A1").Select 'select cell to start at
        ActiveSheet.Paste ' Pastes to our selected loaction
    End With 
     
    ActiveWorkbook.SaveAs n & ".xls" 'Saves our workbook as the name we required
Application.SheetsInNewWorkbook = 1'''limits our new workbook to 1 sheet


    ActiveWorkbook.Close '''Closes the new workbook returns us to our original workbook
End Sub

Display More

Regards,
Simon

Simon Lloyd · Oct 25th 2006

Re: Saving A Worksheet As A File

Just knocked this together it should suit you!

Code

Sub SheetCopy()
     Dim T1
     n = ThisWorkbook.Sheets("Sheet1").Range("G5")' Variable set for naming purposes
     T1 = InputBox("Enter Range To Be Saved" & Chr(13) & "Range must be typed in this fashion Ax:Axx", "Saving Range To New WorkBook")''Inputbox to allow the user to save whatever range they like
    Sheets("sheet1").Range(T1).Copy'''Don't forget to change the source sheet name here
    Workbooks.Add' This adds your new workbook
        Wn = ActiveWorkbook.Name' 'variable set for ease later
        With Wn
        Sheets("Sheet1").Select' 'Select first sheet in new workbook
        Range("A1").Select 'select cell to start at
       ActiveSheet.Paste' Pastes to our selected loaction
        End With
   
    ActiveWorkbook.SaveAs n & ".xls" 'Saves our workbook as the name we required
ActiveWorkbook.Close'''Closes the new workbook returns us to our original workbook
End Sub

Display More

Hope it helps!

Regards,
Simon

Simon Lloyd · Oct 25th 2006

Re: Saving A Worksheet As A File

So you want to copy a range to a new sheet not copy sheet3?, is the range always variable? do you want the user to be able to set the range?

Regards,
Simon

Simon Lloyd · Oct 24th 2006

Re: Save As Method

This activated by keyboard shortcut or comand button would do the trick

Code

Sub SvWbook()
Dim T1
T1 = InputBox("Enter your Save As Name", "Workbook Saver")
ActiveWorkbook.SaveAs T1 & ".xls"
End Sub

regards,
Simon

Simon Lloyd · Oct 24th 2006

Re: Saving A Worksheet As A File

Hi just put this in a standard module use a command button to run it

Code

Sub SheetCopy()
    
     With Sheets("sheet3").Copy
       Application.SheetsInNewWorkbook = 1
     End With
    ActiveWorkbook.SaveAs "Moved Sheet" & ".xls"
End Sub

Regards,
Simon

Simon Lloyd · Sep 20th 2006

Re: Save As Method

In my libraries and references i have the following checked (and im on a network!) Visual Basic For Applications, Microsoft Excel 11.0 Object Library, OLE Automation and Microsoft Office 11.0 Object Library,if these are checked then you need a new install or repair.......and it will have to be IT!

regards,
Simon

Simon Lloyd · Sep 20th 2006

Re: Save As Method

Quote from Username99

So how would I reinstall the library? Thing is, IT installed Office on each workstation and getting something done by our IT department is like getting hell to freeze over, aint happening.

So is there something I can do within Excel to get the library installed?

Unfortunately you probably wont have the rights to install adapt or update your version of office even if you had the office professional disk!................therefore please wait quietly for hell to freeze!

Regards,
Simon

P.S they usually have the whole of foffice stored in a location on a network server from which the update or install!

Posts by Simon Lloyd

Variable Not Assigning Correctly

Variable Not Assigning Correctly

Save & Exit Workbook On Userform Unload Without Prompt

Conditional Formatting: More Than 3 Conditions

Conditional Formatting: More Than 3 Conditions

Locate Text & Copy All Cells Above

Maximum Textbox Value

Maximum Textbox Value

Maximum Textbox Value

Delete Residual Code From Worksheet After Sheet Copy

Saving A Worksheet As A File

Saving A Worksheet As A File

Delete Residual Code From Worksheet After Sheet Copy

Saving A Worksheet As A File

Saving A Worksheet As A File

Saving A Worksheet As A File

Save As Method

Saving A Worksheet As A File

Save As Method

Save As Method