Posts by toecutter

    Re: Messagebox If Cell Format Aquireda



    The space shoulnt be there and "Schedules" is a named range.("G2:G1000")
    Thanks for the reply[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from Kenneth Hobson

    Put this in a module and set the code in Sub t() to your workbook open event if that is what you want.


    Thank Kenneth
    Iam getting a Application-defined or object-defined error message.
    I was also calling the named range incorrectly, the named range is call ("HoursRequired")
    I Have attached the work book


    cheers
    toe

    Hi
    I am trying to pop open a messagebox if a cells interior color index = 3 in a named range.

    Code
    Private Sub Workbook_Open()
    If Sheet1.Range("Schedules").Interior.ColorIndex = 3 Then
       MsgBox "One or more Trainee requires more than TWO HOURS PER WEEK to forefill his log book requirements"
    End If    
    End Sub


    This has got me stumped :(

    Re: Access UserForm Controls From Another UserForm


    sorry

    Code
    Private Sub UserForm_Deactivate()
    Dim cCont As Control
        For Each cCont In UserForm1.Controls
            If TypeName(cCont) = "OptionButton" Then
                Value = False
            End If
         Next cCont
    End sub


    It is the 1st form loaded and remains loaded and i cannot get it to work

    Re: Access UserForm Controls From Another UserForm


    Would it matter that all the optionbuttons are in there own frame on userform1?


    I have tried all these variations without any luck
    thanks
    toe

    Re: Userform Text Box Value Saving On Close!?


    try something like this



    HTH

    hi
    I have a form with a listbox (column count 5) which is populated from a sheet (sheet2) range (a8:f1000).
    A label(lblQuantity) on the form is populated with the listbox's column count 3 upon the double click event.
    The number in the listbox column count 3 is then change by enetring a new number into a textbox(txtNewQuantity) and clicking a command button.
    The problem i have is that if i have 2 or more numbers the same in the listbox column count 3 row it will change the first number that it match's instead of the listbox's number i selected.

    Code
    With Sheet2
        Range("a9:f1000").Select
        Selection.Find(What:=Me.lblQuantity.Caption, After:=ActiveCell, LookIn:=xlFormulas, _
         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
          MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Select
        Selection.Clear '5Shift:=xlUp
        Selection = Me.txtNewQuantity.Value
    End With


    I think i need to use a lookup formula to find the labels value them match 2 more variables from the sheets a and b columns but have no idea where to start


    cheers
    toe
    ps i can attach a book if required

    Re: Bind the Enter Key


    yes excellent shg


    I get out by using the close button


    Code
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Sheet1.Range("a1000").End(xlUp).Offset(1, 0) = Me.TextBox1.Value
        Me.TextBox1.Value = ""
        Cancel = True
    End Sub


    just added a line to clear the textbox
    thanks

    Re: Bind the Enter Key


    There may be a nicer way but this works


    Set Textbox2 proerties to visable=false


    Code
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Sheet1.Range("a1000").End(xlUp).Offset(1, 0) = Me.TextBox1.Value
        Me.TextBox1.Value = ""
    End Sub
    
    
    Private Sub TextBox2_Enter()
        TextBox1.SetFocus
    End Sub

    Re: Bind the Enter Key


    This does the same thing


    Code
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Sheet1.Range("a1000").End(xlUp).Offset(1, 0) = Me.TextBox1.Value
    End Sub


    except i need the focus to remain on textbox1.


    any ideas?

    Re: Move Listbox Line To Another Listbox With A Command Button


    thanks guys.
    iam gana have to leave it for a few days as iam moving apartments.
    I will finish the above code but i found some other code from Will's post
    http://www.ozgrid.com/forum/showthread.php?t=25298
    regarding draging data from one listbox to another which i wouldnt mind checking out.




    cheers
    toe

    Re: Move Listbox Line To Another Listbox With A Command Button


    Why dosnt the below code work?
    The only difference from the above code is the listbox names, sheet name's and range names.


    any ideas
    toe