#1
|
|||
|
|||
Update Userform Captions, TextBoxes, Command buttons From Excel
Hi,
userform experts please advise. I am creating some userforms in word and it has a lot of command buttons, text boxes and labels I was wondering if there was a way for me to map this out on an excel sheet, Example attached So that when I change the values in excel , I can run a macro to update. Or I don't mind storing it in a word table - if that's a possibility I am pretty new to userforms, so I'm not sure, what I should do apart from manually set every property which will take forever. Advice very much appreciated. dan |
#2
|
||||
|
||||
The short answer is yes you can do this, with a couple of provisos, and it will be faster from Excel. The first is that you should have the names of the command buttons in the first column and not merely numbers.
Command Button Caption CommandButton1 Edit Text CommandButton2 Add Column CommandButton3 Delete Column CommandButton4 Delete Table CommandButton5 Font Resize The second is that I would only do this if the names changed frequently (otherwise it is better to name the buttons either directly or in code). That being the case, you could use the following macro (in an ordinary module) to call the userform and name the buttons. Code:
Option Explicit Const strWorkbook As String = "C:\Path\Forums\buttons.xlsx" 'The workbook path Const strSheet As String = "Sheet1" 'The name of the worksheet Sub Example() 'Graham Mayor - www.gmayor.com Dim oFrm As New UserForm1 Dim Arr() As Variant Dim oCtrl As Control Dim i As Long Arr = xlFillArray(strWorkbook, strSheet) With oFrm For i = 0 To UBound(Arr, 2) For Each oCtrl In .Controls If oCtrl.Name = Arr(0, i) Then oCtrl.Caption = Arr(1, i) Exit For End If Next oCtrl Next i .Show End With lbl_Exit: Set oCtrl = Nothing Exit Sub End Sub Private Function xlFillArray(strWorkbook As String, _ strWorksheetName As String) As Variant 'Graham Mayor - www.gmayor.com Dim RS As Object Dim CN As Object Dim iRows As Long strWorksheetName = strWorksheetName & "$]" Set CN = CreateObject("ADODB.Connection") CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES"";" Set RS = CreateObject("ADODB.Recordset") RS.Open "SELECT * FROM [" & strWorksheetName, CN, 2, 1 With RS .MoveLast iRows = .RecordCount .MoveFirst End With xlFillArray = RS.GetRows(iRows) If RS.State = 1 Then RS.Close Set RS = Nothing If CN.State = 1 Then CN.Close Set CN = Nothing lbl_Exit: Exit Function End Function
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Graham,
Boss Respect!! thank you very much! I thought I would give it a shot as I am no coding expert, I wasn't too sure this could be done. But this helps me to plan and map my buttons, text boxes labels. The canvas is very small and it is in dire overpopulation status with all the buttons and labels - it becomes really difficult to move things around and edit. Assigning wrong captions to the wrong items etc They say every little helps, but every big help helps. This will help me so much. You really made my FRIDAY!! Thanks ever so much again for your code dan Last edited by dan88; 05-20-2016 at 09:03 AM. |
#4
|
|||
|
|||
Hi Graham,
with a little tweak, i have been able to get this to change the buttons on the Canvas. It will set the actual button names in the properties. Code:
Sub CanvasButtons() 'Graham Mayor - www.gmayor.com Dim oFrm As New UserForm1 Dim Arr() As Variant Dim oCtrl As Object Dim i As Long Arr = xlFillArray(strWorkbook, strSheet) With ThisDocument.VBProject.vbComponents("UserForm1").Designer For i = 0 To UBound(Arr, 2) For Each oCtrl In .Controls If oCtrl.Name = Arr(3, i) Then oCtrl.Caption = Arr(4, i) oCtrl.Height = 100 oCtrl.Width = 300 oCtrl.BackColor = RGB(0, 127, 127) ElseIf oCtrl.Name = Arr(5, i) Then oCtrl.Caption = Arr(6, i) ' I may add lots more ElseIfs :) End If Next oCtrl Next i '.Show End With lbl_Exit: 'Set oCtrl = Nothing Exit Sub End Sub Happy coding Sunday dan solved |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Captions: Changing captions in Appendix update all captions | carnestw | Word | 3 | 10-27-2015 12:34 PM |
Can't update/refresh embedded Excel file with Power Point 2010 command button | mt1013 | PowerPoint | 0 | 06-17-2015 05:18 AM |
VBA Code in a UserForm module to delete a Command Button which opens the userform | Simoninparis | Word VBA | 2 | 09-21-2014 03:50 AM |
UserForm Search with multiple TextBoxes | johndough | Excel Programming | 0 | 05-10-2014 12:11 PM |
VBA code to update record in Access 2003 using Userform in Excel | primmer3001 | Excel Programming | 0 | 08-29-2011 04:25 PM |