Update multiple textboxes and cells

SIXTH SENSE

Well-known Member

Joined

Oct 29, 2003

Messages

1,883

• Jul 9, 2004

• #2

Hi!
Welcome to the board!
Try this!

Code:

Sub TextboxesToCells()
Dim sh As Shape
For Each sh In Sheet1.Shapes
If UCase(sh.Name) Like "*TEXTBOX*" Then
   Range("A" & Val(Mid(sh.Name, 8))) = ActiveSheet.OLEObjects(sh.Name).Object.Value
End If
Next sh
End Sub

 

Upvote 0

A

artzfx

New Member

Joined

Jul 7, 2004

Messages

6

• Jul 9, 2004

• #3

Thankyou for the quick reply, however I can't follow what you have suggested. Not sure what shapes refer to or where I should reference my text box and cell numbers.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Jul 9, 2004

• #4

Try this

Private Sub BtnUpdate_Click()
Dim I As Integer

For I = 1 to 125

Sheets("Sheet1").Range("A" & I).Value = Me.Controls("Textbox" & I).Text

Next I
End Sub

This assumes BtnUpdate is on the same Userform as the all the textboxes.

 

Upvote 0

A

artzfx

New Member

Joined

Jul 7, 2004

Messages

6

• Jul 9, 2004

• #5

Thanks Norie it works great.

My textboxes are on various pages of my MultiPage control and the Update button is on the Userform itself. It still works, however should I be referencing the MultiPage1 as part of my code?

Thanks again.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Jul 9, 2004

• #6

If it works I would just leave it.

The Controls collection of the Userform contains all the controls and I don't think they are grouped in any way.

 

Upvote 0

A

artzfx

New Member

Joined

Jul 7, 2004

Messages

6

• Jul 9, 2004

• #7

Cheers Norie.

Just noticed where you are from. I am heading to that part of the world next month for my brothers wedding at Wedderburn Castle (Duns). Small world...

 

Upvote 0