Posts by Naf

  • Fill ComboBox With Only 1 Occurence Of Each Item

    Hello all,


    I'm not sure if this is possible or not but I think you guys and girls will be able to help me.


    The ideal is I have a list which the use fills in, for sack of argument Goal 1, Goal 2, etc but I have a problem. This list which the user builds I want to appear in a combobox with is quite easy using the list function and naming the range.


    The problem I have is that Goal 1 or 2 can be in this list more than once, if it is at all possible I want or would like the Combobox to only show Goal 1 once and not twice or how many times it occurs. I require the Combobox just to show all Goals once no matter how many time they occur.


    Am I asking the impossible if not can some one point me in the right direction?


    Thanks


    Naf

  • Changing Colors On A Multipage Form

    Re: Changing Colors On A Multipage Form


    Hi,


    I think your right I have looked every where for to try and do this, the only draw back is I have created the lay out and you can't change the colour of the tabs.


    Thanks for the help


    Naf

  • Changing Colors On A Multipage Form

    Afternoon All,


    I have several forms each with Black Backgrounds and White text but I have now come across a problem, I have had to use a multipage on one of the forms to hold all the information.


    The problem is as Default the text is black and the form is grey which doesn’t match to all the other forms, the question is can I change the text to while and the back ground to black on all the pages on the multipage form. I have been trying but I'm unable to find a way so far, I'm using Excel 2003 if that makes a diffrence


    Thanks


    Naf

  • Forecolour Options Are Locked

    Re: Forecolour Options Are Locked


    Yep tried BackColor and it still can't change the colour, their doesn’t seem to be any options (no drop down menu) I have tried on a friends machine and I can change the colours fine?

  • Forecolour Options Are Locked

    Hello,


    In the Properties for every Text box I have on my forms it's not letting me change any of the colours but I can change the fonts etc, I'm using Excel 2003, can any one help?


    Thanks


    Naf

  • Activating A New Form Once A Selection Is Made From A Form

    Re: Activating A New Form Once A Selection Is Made From A Form


    Hello,


    Which ever way I try to achieve this it doesn’t seem to like the command 'show'


    Code
    Private Sub Worksheet_Activate()


Sheets("Open").Visible = xlVeryHidden


Clients1.Show


End Sub


    I have also tried


  • Activating A New Form Once A Selection Is Made From A Form

    Hello all,


    I have a form with Option Buttons and once a selection has been made it opens the relevant sheet on the workbook but I can get it to open the relevant form to feed that worksheet once the selection has been made from the opening form. Is it possible to do this and if so dose anyone no the code.


    Thanks


    Naf

  • Clearing Contents Of Cells

    Re: Clearing Contents Of Cells


    Hi,


    I broke the code down into two lines so I could see at which point it was breaking down as having the line below it still didn't work, Sorry new to VBA programming


    Code
    range("A17:S17").CearContents

  • Clearing Contents Of Cells

    Hello,


    My computer runs on Excel 2003 but my Laptop is on Excel 2000, the problem is I have the below code on a spreadsheet I wrote on my Computer which runs fine but when I open it on my Laptop it comes up with a run time error it doesn’t like the clear contents line.


    If some one could explain to me why it is happening and how to get round the problem so it will work in Excel 2003 and 2000 that would be much appreciated.


    Thanks


    Nathan


  • Locking A Text Box If It Has Text In It Only

    Hi All,

    Is it possible to lock a textbox on a form if the cell contains text but if it doesn’t contain any information it stays open for a user to add information to the textbox/cell and locks next time the form is view.

    Thanks

    Nathan

  • Find A Value And Offsetting

    Re: Find A Value And Offsetting


    I have worked out the answere please see the code below


    The only problem is that I can't get it to to the same when you getting the word to serch from a text box. As soon as you enter a value it only excepts the first thing entered and then falls over


    Thanks


    Naf


    Code
    Sub TestModule()
Columns("E:E").Select
Selection.Find(What:="Bt100", After:=ActiveCell).Offset(, -1).Value = "1"
End Sub


    Code
    Private Sub TextBox1_Change()
Columns("E:E").Select
Selection.Find(What:=TextBox1.Value, After:=ActiveCell).Offset(, -1).Value = "1"
End Sub

  • Find A Value And Offsetting

    Re: Find A Value And Ofsetting


    Quote from Reafidy

    Hi try this,


    Code
    Sub TestModule()
    Dim c As Range
    Set c = Columns("E:E").Find(What:="Bt100", After:=ActiveCell, LookIn:=xlFormulas, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then c.Offset(, -1).Value = "1"
End Sub


    Thanks Reafidy,


    I have ran the code but it doesn’t seam to work and when I run the debug it highlights “Set c” to “MatchCase” with the arrow on the last line. If you have any ideals of what is causing the problem, it would be much appreciated as I was having the same problem yesterday.


    Thanks


    Naf

  • Find A Value And Offsetting

    Hello,


    I'm trying to run a search and when it finds the value "Bt100" it adds a '1' into the cell on the left.


    The code which I can't get to work is below


    Thanks


    Naf


  • Placing A "1" To The Left Of A Urn

    Re: Placing A "1" To The Left Of A Urn


    Quote from StephenR

    For example:


    could be replaced by

    Code
    With Sheets("Incidents")
    .Range("C9").FormulaR1C1 = "=VLOOKUP(RC[-1],data,2,FALSE)" 
    .Range("D9").FormulaR1C1 = "=VLOOKUP(RC[-2],data,3,FALSE)" 
    .Range("E9").FormulaR1C1 = "=VLOOKUP(RC[-3],data,4,FALSE)" 
End With


    etc


    Thanks for that I have cleaned the code up now it's alot easier to follow but still have teh same problem as befor I can't get the '1' to appera

  • Placing A "1" To The Left Of A Urn

    Re: Placing A "1" To The Left Of A Urn


    Quote from royUK

    You code needs tidying up, you Select the sheet Incidents repeatedly, when three is no need to select at all, also the scrolls can be deleted.
    Why are you adding formulas then pasting as values? If you are going to use VBA then use the VBA to add the value directly, I can't really say how without knowing exactly what you are doing.


    I trying to pull the information from the "Data" sheet to the "Incident" sheet so it can be view and changed then if you are happy with the change you click the update button and it changes the original information on the "Data" sheet and removes it from the "Incident" sheet. Hope this makes some sense new I should have read the book from beginning to end and not missed out the middle bit lol


    I will have another look over my code and clean it up but the other thing that might put another spanner in the works is that I want to hide the "Data & Incidents" sheets and I have a feeling that that is going to mess up my coding


    Thanks


    Naf

  • Updating Along A Reference Point

    Hello All,


    Once I have created a reference point I'm having trouble updating along that row.


    This is the code I'm using I would be very grateful if some one could help me as for my first project I think I have bitten off to much but hay it stops me from getting board.



  • Placing A "1" To The Left Of A Urn

    Re: Placing A "1" To The Left Of A Urn


    Quote from Wigi

    The parentheses were at the wrong spot:


    [vba]Set rng = Sheets("data").Range(Cells(1, 20), Cells(Rows.Count, 2).End(xlUp))[/vba]


    The code is still falling over I have attached the complete code I have be using as it might be more help and is proberly the long way of doing things


  • Placing A "1" To The Left Of A Urn

    Re: Placing A "1" To The Left Of A Urn


    Quote from royUK

    Wigi is right, there is a typo.


    You don't need the Selections, nor to search the whole column. No speech marks around Textbox1 either


    Code
    Dim rng As Range
Dim cl As Range
Set rng = Sheets("data").Range(Cells(1, 20, Cells(Rows.Count, 2).End(xlUp)))


 Set cl = rng.Find(What:=TextBox1.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
cl.Offset(0, -1) = 1



    Hi RoyUK,


    For some reason an error is coming up it doesn’t like the word 'Cells' in the third paragraph