Changing Text Box Reference

[Airbisk]

Hi,

I have a sheet called Main with about 50 text boxes that have a formula that links a value from another sheet (e.g. =References!A11)

I have some text boxes (buttons) that run a macro that populate the 50 text boxes with different values. (The buttons used are for Name, Location and Age) So if you click on Name, each of the 50 text boxes will have a different name in it.

What I am finding is if you select a button and then another button, some of old values from the previous selection remain in the text boxes as though it's not refreshing properly.

What I thought of doing is when the buttons are pressed to populate the text boxes, the references are populated also by the marco so kind of forcing the value to pull through.

I cannot seem to get the correct way of adding the reference in the text box to point to the cell and have tried different way but with no joy.

Are you able to shed some light please. I have tried 3 different ways to reference the cell but failed, see Code below.

Many Thanks in advance

Sub arraypops()

Range("a1").Select
ActiveCell.Value = ref1

Sheets("references").Select
Range("a3:a50").ClearContents

Range("a3").Select
ActiveCell.Offset(rowOffset:=0, columnOffset:=array1).Activate

refer = ActiveCell.Value

Do Until refer = ""
ActiveCell.Offset(rowOffset:=0, columnOffset:=-array1).Activate
ActiveCell.Value = refer
ActiveCell.Offset(rowOffset:=1, columnOffset:=array1).Activate
refer = ActiveCell.Value

Loop


Range("References!A3").Value = Sheets(Menu).TextBox17.Value
Sheets(Menu).TextBox17.value = Sheets("References").Range("A3").Value
Sheets(Menu).Shapes("TextBox17").Value = Sheets("References").Range("A3").Value

Sheets("Menu").Select
Range("d3").Select

End Sub

 

[SkipVought]

hi,

I have a sheet called Main with about 50 text boxes

That is INSANE!

Why would you use TEXTBOXES on a spreadsheet?

But be as it may, each control (shape) has a TopLeftCell and BottomRightCell property.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Airbisk]

Thanks Skip,

There is some method to the madness!!

Once the text boxes are populated these then become buttons that once pressed will pull out information from a data sheet into a Results sheet.

So if you would like to look at information by name, click the main Name button, this then populates the names into the text boxes, you can then choose the name you would like more information on this then runs another maco that find the name in the data sheet and dumps it into results.

It has been working finen this way, its just those text boxes with old value not refreshing.

I will check out you suggestion.

Many thanks for your reply.