Posts by Simon Lloyd

Re: Displaying More Than One Result In A Message Box?

Andy thanks for the very quick response, just a couple of learning curve questions is vblf the same as Chr(13) and does Len mean length so where you state

If Len(strText) > 0 Then

is it if length of string is > 0 then input string?

Regards,
Simon

Hi all, i have a routine which loops through a range looking for past dates and when it finds one display the result in a message box in my testbook i only use 25 rows but it gets annoying having to click ok for every find....is it possible to collect all results and display them in the message box at one time?

Private Sub Workbook_Open()
Dim Mycell
Dim Rng
Set Rng = Sheets("Sheet1").Range("B1:B25")
For Each Mycell In Rng
If Mycell.Value < Date Then
MsgBox Mycell.Offset(0, -1).Value & " Is Overdue By " & Date - Mycell.Value & " Days, Take Action Now!", vbOKOnly, "Tasks Overdue"
End If
Next Mycell
End Sub

Regards,
Simon

Re: Detect Cell Address And Pass Range.

the code you have looks at one sheet and then pastes to the activecell of a new sheet you have added, below is some code that works all on the same sheet

Sub SelectRange()
    Dim EndRange
    Range("A1").Copy
    Cells.Find(What:="cata", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                                                                  :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
               False, SearchFormat:=False).Select
    ActiveCell.Offset(0, 10).Select


    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

this is because i am not entirely sure what you are trying to achieve.

Regards,
Simon

Re: Worksheet Formula If Statement Involving And/or?

Ian thats it worked well thanks for your prompt replies!

Regards,
Simon

Re: Worksheet Formula If Statement Involving And/or?

Thanks Ian i had already tried both of those, the first shows false if B1 shows off and D1 shows on but if they both show on the name from sheet 2 appears.

With the second formula it says there is a formula error, which i also got when i tried it before posting.

Any other ideas?

Regards,
Simon

Hi all, i am trying to create what is probably a simple If statement for a worksheet formula i want to check to see if one of two cells have the text specified if so then the cell value is A4 from another sheet if not the cell containing the formula should be blank, something like below

=IF(B1="on" Or D1="on",Sheet2!A4,"")

I also need this kind of formula with the AND operator, something like

=IF(B1="off" AND D1="off",Sheet2!A4,"")

i have tried varying combinations but had no joy, any suggestions?

Regards,
Simon

Re: Lock Certain Cells By Vba

Hi girly you have already asked this question in another thread and got some useful answers, i have attatched your modified workbook and a short explanation.

Sub lockcells()
  Dim Rng 
    Dim MyCell 
    Set Rng = Range("A1:Q53") 
    For Each MyCell In Rng 
        If MyCell.Value = "" Then 
             
        Else: ActiveSheet.Unprotect Password:="password"
            MyCell.Locked = True 
            MyCell.FormulaHidden = False 
            ActiveSheet.Protect Password:="password", UserInterFaceOnly:=True 


        End If 
    Next 
End Sub

Regards,
Simon

Re: Referencing Files

Stephen this is how i have mypath set in one of my workbooks

MyPath = "E:\Excel Workings\Test Bed\First Test"

im afraid thats the limit of my experience with setting paths.

Regards,
Simon

Re: Write Data To Last Used Rows

You could use this in the code

Dim Wks
Set Wks = ActiveSheet.Name
For lLoop = 1 To MAWBNoVar
    With Wks.Range("B" & Rows.Count).End(xlUp)
        .Offset(1, 0) = Controls("txtMAWBNo" & lLoop)
        .Offset(1, 1) = Controls("cbDestination" & lLoop)
        .Offset(1, 2) = Controls("txtGW" & lLoop)
        .Offset(1, 3) = Controls("txtCBM" & lLoop)
    End With
Next lLoop

but im no expert....Dave is!

Regards,
Simon

Re: Referencing Files

in your path there is a blank so it looks like your path starts with no name

Quote

VBA:
mypath = "\\Eo1\r_i\Surveys\NALC\Returns\"
ChDrive mypath

im no expert but where you have "\\ is probably causing the problem if the rest of your folder names are correct.

Regards,
Simon

Re: Lock Used Cell. Unlock Blank Cells

Oooops! sorry Dave i did forget to say that i unlocked all cells on the worksheet first!

I dont know why i didnt think to just unlock blank cells, the way my code works is as soon as you have entered data in a cell it becomes locked, i was unsure whether she needed to do this or just find cells that already have data and just lock these but no further cells.

Regards,
Simon

Re: Locking Cells In A Sheet

Hi this will do what you ask but the larger the range the slower it will perform!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng
Dim MyCell
Set Rng = Range("A1:Q53")
    For Each MyCell In Rng
        If MyCell.Value = "" Then
           
        Else: ActiveSheet.Unprotect
            MyCell.Locked = True
            MyCell.FormulaHidden = False
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
            ActiveSheet.EnableSelection = xlNoRestrictions
        End If
    Next
End Sub

Regards,
Simon

Re: Cut Text From String At First Space And Paste Elsewhere

you dont need vba really a worksheet function would do it like this copy this into A1

=LEFT(B1,FIND(" ",B1)-1)

where B1 is where your original word is this should now return the first word from your string, then copy down and hey presto single words trimmed from your string!

Regards,
Simon

Re: Some Submenus Disabled

Try this in a standard sub:

Sub MenuBar_Restore()
   CommandBars("Worksheet menu bar").Reset
End Sub

it should reset the worksheet menu bar back to normal!

Regards,
Simon

Re: Writing Data To Open Workbook

Hi, you havent explained very much or provided a sample, you could really do with posting your code or worbook, but the code below will paste from cell A1 on the sheet that you are viewing in your workbook and paste to Book2.xls (provided it is open) next available cell in column A the provide you with the cell address then once you click ok take you back to your original workbook.

Sub PasteOpenBook()
Dim OriginalWorkbook
OriginalWorkbook = ActiveWorkbook.Name
Range("A1").Copy
Workbooks("book2.xls").Activate


With ActiveWorkbook
    Sheets("Sheet1").Select
End With
With Selection
    Sheets("Sheet1").Range("A65536").End(xlUp)(2).Select
    ActiveSheet.Paste
End With
Application.CutCopyMode = False
MsgBox "Last paste was at " & ActiveCell.Rows.Address
Workbooks(OriginalWorkbook).Activate
End Sub

Hope this helps,
Regards,
Simon

Re: Range Function Vba

I tried your code below in a standard module, it worked fine!, would you like to post all of your code?

You say one sheet has a combobox control? do you mean a command button and combobox or just a combobox?

What is it you click to initiate the procedure?
Where is it located?

Regards,
Simon

Re: Clear Contents & Change Color Of Cells

Hi green, although my code isnt elegant it does change Allthe entries for the booking number.

Regards,
Simon

Re: Trigger Filter With Cell Change

Ok got it sorted just drop this in place of your TextBox1 code

Private Sub TextBox1_Change()
    With Sheet1.Range("A14:A31")
        If TextBox1 <> "" Then
        .AutoFilter field:=1, Criteria1:="<=" & TextBox1.Value, visibledropdown:=False
       Else: AutoFilterMode = False
       End If
    End With
End Sub

you will find that as soon as you type a number it will filter and show all results that number and below, there is no autofilter dropdown visible, if you clear the textbox then the selection becomes unfiltered, an autofilter will only show up to 1000 entries if you have a large database there are some other ways around it but not as elegant!

You will have to specify the entire range that you want filtered as i have done in the with statement.

Regards,
Simon

Re: Trigger Filter With Cell Change

Hi change of plan!, from the worksheet choose View then Toolbars then Control Toolbox click on Command Button and then place it on your worksheet next to A11, now right click the button and choose view code and paste this in:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Range("A11")
If rng.Value <> "" Then
    With Sheet1
        .AutoFilterMode = False
        Range("A13:A31").Select'''This is your range change it to suit
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=Sheets("Sheet1").Range("A11").Value
 
    End With
 ElseIf rng.Value = "" Then AutoFilterMode = False
 End If
End Sub

if A11 is blank and you click the button then the autofilter will be turned off, if it has a value in then the range will be filtered by that value.

Regards,
Simon

Re: Error 91 When Closing

Hi, it sounds as though you have not referenced your worksheet, you have this

Dim i As Long 
    Dim wks As Worksheet 
    Set wks = Worksheets(7) 
     
    On Error Goto errhandler

but in the code you supplied you neither use any of them or reference them, its possible that you need to use a "With" statement, from the code you posted those are the apparent errors but ideally you need to post your workbook.

With the description you have given and snippet of code its hard to give a solution or suggestion.

Regards,
Simon