Posts by johnyc

Re: Dymanic Hyperlink based on cells value

Thank you! It worked like a charm!

Hi,

I have a specific table with the master data of the products. The table contains product ID and description. In another sheet the user enter random product IDs. For each product ID he enters I want to automatically convert the cell to hyperlink to the correct cell of the product table.

I use the Worksheet_Change function to identify the range of cells that a user changes but with no success.

Private Sub Worksheet_Change(ByVal Target As Range)


    If Not Intersect(Target, Range("A7:A10")) Is Nothing Then    
        Dim r As Range
        For Each r In Target.Cells
            Range(r.Address).Value = _
             "=HYPERLINK(""#Products!""&ADDRESS(MATCH(" & r.Address & ";Products!$A:$A;0);1);" & r.Address & ")"
        Next
    End If
End Sub

I have a cell with data validation. The width of the column is specific and must not change. As such when I press the drop down list i cannot see the full text. How can I change the width of the drop down menu or put an horizontal scroll bar? I am seen some workarounds e.g. temporary change width of column but do not want this.

Re: Link Textbox object with cell

thanks for the reply. The issue was that my sheet had two words and I had to put the inside '' (e.g. 'Sheet Name'!A1).

Re: Link Textbox object with cell

I have tried the following code but it does not seems to work...

Private Sub TextBox1_Change()
TextBox1.LinkedCell = Worksheets("Sheet1").Range("A3").Value




End Sub

I forgot to mention that the link is located in another sheet from the one that the TextBox is.

Hi, I have created an ActiveX textbox and I want to link it with a cell in order to feed data. In addition, every time I change a specific cell (with data validation applied) I want to refresh the textbox (because the contents of the linked cell change).

Re: Resize shape based on cell position

Thank you very much! This is what I was looking for!

Re: Resize shape based on cell position

Sorry if I wasn't clear enough...

With the above code that you proposed you set the height of the shape to be the same with the height of a specific cell (A1). I want to resize my shape as follows:
Let's say that my shape shape ends at cell D11. I want to resize it somehow and make my shape ends at the cell E20.

Thanks again for your help.

Re: Resize shape based on cell position

This isn't exactly what I am looking for. I don't want to resize the shape according to a specific cell's heightforum.ozgrid.com/index.php?attachment/56867/.... Please refer to the attached file.

I want to resize a shape and set the height to a specific cell. How can I achieve that?

Re: Automatic update cell based on the values of another cell

This is was I was looking for! I hadn't realised it was so easy...

Thank you very much!

Re: Automatic update cell based on the values of another cell

Please refer to attachment
forum.ozgrid.com/index.php?attachment/56818/

I have one cell in which I have applied data validation rules. When the users selects one of the permitted options I want to update the values of 3 other cells (automatically). Is there any way to achieve that?

Re: Find several values within a range of cells

If I implement it through vba what code should I use?

A have a list with values that I want to find if they exist within a specific range of cells.

I am guessing that I have to initialize an array with my values and somehow search on the specific range that I want.

The sub should return a boolean value.

Re: Identify values to a dynamic table

Can you also provide me with the vba code in order to add the first name range?

Thank you again

Re: Identify values to a dynamic table

Thank you very much!

It was very helpful.

Re: Identify values to a dynamic table

Can you please provide to the solution with a small example to an excel file?

Thank you!

I have an Excel file with many categories at rows. Every category is unique and is located in a different row (e.g. cat 1, cat 2, cat 3 etc.). For every category there are several items beneath. For some categories the items may have the same name. At the next column I have the corresponding value for each combination.

I have a list with combinations of categories and items which I want to match and identify their respective value. I want to implement this through a function.

My only problem is that the file with the data changes dynamic and I don't know each time in which cell I will find every category and item. Are there any ideas?

Here is an example:
Column A Column B
category 1 14
item 3 29
item 4 20
item 7 45

category 2
item 4 34
item 5 20
item 7 30