Posts by allancar

    Have solved mystery . I had manually entered two lines of Data in the "database" when I removed those and populated from userform/textbioxes every thing works fine. Looks as if combobox will also do the trick

    but still cannot get the following to work. keeps returning a tick in "Female" optionbutton. And still not not sure where it should fit in first code

    1. If ws.Cells(i, 26).Value = "Male" Then _
    2. OptionButton1.Value = True
    3. Else ws.Cells(i, 26).Value = "Female" Then _
    4. OptionButton2.Value = True

    Thanks for that Roy but due to the way I have set up the workbook , I am trying to present everything in a uniform way. Userforms with textboxes suit this situation "presentation wise" better. The same userform is used to save the data in the first instance and I want the user upon entering the name be able to immediately see any existing record.

    thanks again for you help

    This is probably very simple but this novice can.t see where i'm going wrong

    I have a Userform with a number of Textboxes that are filled with data from a separate sheet. On inputing a value(in this case a name) in Textbox1 the code looks in column 22 on another sheet and when a match is found takes corresponding data to the left and fills each separate Textbox. All works fine except it does not find or pick up the first two rows of data.

    If the data is as follows:

    Col 22 col23 col 24 etc

    row 3 Allan Data Data ....

    row4 Janet Data ''

    5 David '' ''

    6 Bob '' ''

    7 John ' ''

    And I enter David,Bob or John all text boxes are successfully filled but if I enter Allan or Janet there is no response

    starts in row is 3 and column 22 (v).

    Below is code

    I am also trying to populate two option buttons by using the following but are not sure if this code is correct or where too insert it in above code

    If ws.Cells(i, 26).Value = "Male" Then _
    OptionButton1.Value = True
    Else ws.Cells(i, 26).Value = "Female" Then _
    OptionButton2.Value = True

    Any assistance is gratefully accepted

    Have installed the code in the workbook and with one or two small changes it is working very well .

    I changed active cell range,

    and Played around with this section of main code

    Cells(found.Row, found.Column + 1), Sheets("Data").Cells(found.Row, found.Column-1))

    changed to

    Cells(found.Row, found.Column + 1), Sheets("Data").Cells(found.Row, found.Column)

    Also removed shift:=xlUp after Delete as Delete automatically moved the row up

    Didn't use the section of code that cleared the actives cell but just refreshed the tabs.

    I also changed a number of formulas to use OFFSET to avoid problems created when shifting cells up.

    Have attached final workbook for your perusal.

    The RANGE FIND solution always looked like a can of wiggle worms but now I at least have some idea how it works.

    Just got to sort out printing a report.....

    thank you for your invaluable assistance.

    copy for ozgrid 3.xlsm


    I have set this up so inputs from the "add to library" boxes on the "Gearlist" worksheet are saved to the "Gear library" on the Data worksheet and also to the various tabs on the " Library list" in the Gearlist worksheet. I then can select an item in this list (using the various tabs ) click Add and it is transposed to the corresponding tab on the right (Pack List).

    What I want to do now is select an item from that Pack List (Gearlist worksheet) and then on pressing the Remove button delete that record from the" Pack Contents List" in Data worksheet and also then move any data below up 1 place. The item will still remain in the Gear Library for future select.

    If I use the reference cell (m21) to identify which Tab the item is in and adapt your code as follows do you think this would work.

    Rnge = Range("m21").Value

    if Rnge="pack" then

    Sheets("Data").Range("ppack" & ActiveCell.Row).Delete Shift:=xlUp

    Elseif rnge="shelter" then

    Sheets("Data").Range("pshelter" & ActiveCell.Row).Delete Shift:=xlUp

    ect ect

    or is there an easier way


    copy for ozgrid 2nd.xlsm

    Thanks for your help Carim. Code does sort off work but was causing problems with the way I had store the data (stacked in vertical ranges) column delete was mucking up any data store below . so I have place data ranges in a horizontal fashion. I have attached the worksheets in question so you can see better want i am trying to achieve. when I select a item in the pack list block of tabs and click the remove button the corresponding entry in the "Data" Packlist should be delete and any remaining data move up one row. it is not necessary now for the selected entry in the Pack list Tab to be delete as this will happen on an update happens (when this code is written:))

    Please forgive me for any rough coding in the worksheets, as I am a complete beginner.

    thanks again for you assistance.

    sorry roy but can't seem to find how to post a workbook. Was hoping my last post would help show what i am trying to do

    Thanks Carim for your code but the idea is for only the cells containing the item selected be clear not entire rows (as this will clear other data).

    Furthermore the selected cell will be in worksheet "stocklist" and the targeted cell will be in worksheet"data"

    I also want to using a command button click to instigate the code.

    Thanks for having a look at this. I was using vlookup as i was thinking that with its use i could find the corresponding entry in the 2nd worksheet and then delete its contents. However if i am incorrect and there is a better way to achieve the result I'm after I will be please to be corrected as I am a total newbie.

    Below is a display of what I am trying to achieve

    Worksheet "Data" ----|---- Worksheet "stocklist"

    Range "ppack"-----------|---- Range n22:n40


    item1-----------------------|---- item1

    item2-----------------------|---- item2 selected item

    item3-----------------------|---- item3

    item4-----------------------|---- item4

    command button click


    Worksheet "Data"----|---- Worksheet "stocklist"

    Range "ppack"---------|---- Range n22:n40

    item1----------------------|---- item1

    item3 ---------------------|---- item3 Item has been removed and cells has moved up
    item4 ---------------------|---- item4

    Is it possible to move up only the contents of the two ranges and not any cells below and outside their ranges?

    thanks again for your assistance

    Hi all, I have setup 2 worksheets one containing Data("Data") and one ("stocklist") displaying selected items from the data sheet. What I am trying to achieve is on selecting an item in the display list (Worksheets.Stocklist.Range(n22:n40) and using a command button delete that selection along with its listing in the Data sheet in the Range("ppack"). Is it also possible to move remaining data in only the corresponding ranges to fill empty cell?

    I have been trying ,with no success, to use vlookup as it is only a small data set.

    Thanks for that works fine. I also tried the following code line which also worked

    ActiveCell.Copy Worksheets("Data").Range("ppack").Find("")

    Thanks again for your assistance

    Moderation Edit: Please not I have put your code in a special Code Box which makes the code easier to read and copy. Please use them in future, watch this 60 second video to see how

    Posting Code on Ozgrid

    Hi folks a total newbie here. i am having problems using Activecell. Am trying to get, through the use of a command button, the value of a selected cell to copy to another sheet.

    Have check the first part of code and it works fine but the second part returns a Error >>. Object doesn't support this property or method.

    I have tried the varies follow lines with no luck (not all at same time:)) '

    What am I missing?

    Some examples of what I have tried.

    Worksheets("Data").Range("ppack").Find("").Value = Worksheets("Gearlist").ActiveCell.Value
    Worksheets("Data").Range("ppack").Find("").Value = Worksheets("Gearlist").Range(ActiveCell.Value).Value
    Worksheets("Data").Range("ppack").Find("").Value = Worksheets("Gearlist").Range(ActiveCell.Value).Value
    Worksheets("Data").Range("ppack").Find("").Value = Worksheets("Gearlist").Range(ActiveCell.ActiveCell.Offset()).Value

    Thanks in Advance