southcraven
New Member
- Joined
- Dec 1, 2014
- Messages
- 7
Hi,
First thanks to all most of my code is taken from the site, & I am not experienced in VBA yet.
I have a database in Excel (Access not available to all the users). The database represents different land survey control points, at different locations. The format of each sheet (except a summary sheet called Control) is identical, each sheet is a different location, sheet name is the location. Each row has different values and includes 3 images, column "A" is the control point name, and so on, Row 1 being the field names. The VBA code should when a User opens the workbook, display a Form, in ComboBox1 the users selects a location, which automatically fills ComboBox2. So far I have the form, and have coded the combo boxes to fill correctly. It is not elegant code but it works, I would prefer to have a dynamic code rather then Case Select to fill ComboBox2.
On the form is a cmd button which is hard coded to a particular sheet and selects using autofilter the control point selected in Combobox2. Again would like the work sheet to be selected dynamically based on the Combobox1 value. Even though the row with the correct data is selected, the User cannot see it and must find it themselves, again would prefer that the row is not filter just highlighted.
Below is the UserForm code
Finally once the user has confirmed that the data contained in the particular row is correct I want to copy and paste to a word template. So far the code I have appears to work but I really need it to be dynamic, i.e. instead of using cells as shown below I want the use the row selected in the UserForm automatically, especially the images.
First thanks to all most of my code is taken from the site, & I am not experienced in VBA yet.
I have a database in Excel (Access not available to all the users). The database represents different land survey control points, at different locations. The format of each sheet (except a summary sheet called Control) is identical, each sheet is a different location, sheet name is the location. Each row has different values and includes 3 images, column "A" is the control point name, and so on, Row 1 being the field names. The VBA code should when a User opens the workbook, display a Form, in ComboBox1 the users selects a location, which automatically fills ComboBox2. So far I have the form, and have coded the combo boxes to fill correctly. It is not elegant code but it works, I would prefer to have a dynamic code rather then Case Select to fill ComboBox2.
On the form is a cmd button which is hard coded to a particular sheet and selects using autofilter the control point selected in Combobox2. Again would like the work sheet to be selected dynamically based on the Combobox1 value. Even though the row with the correct data is selected, the User cannot see it and must find it themselves, again would prefer that the row is not filter just highlighted.
Below is the UserForm code
Code:
Private Sub ComboBox1_Change()
Me.ComboBox2 = ""
Select Case Me.ComboBox1
Case "A_Island"
Me.ComboBox2.RowSource = "A_Island"
Case "D_Island"
Me.ComboBox2.RowSource = "D_Island"
Case "EPC2_"
Me.ComboBox2.RowSource = "EPC2_"
Case "EPC3_"
Me.ComboBox2.RowSource = "EPC3_"
Case "EPC4_"
Me.ComboBox2.RowSource = "EPC4_"
Case "OPF"
Me.ComboBox2.RowSource = "OPF"
Case "EWRP"
Me.ComboBox2.RowSource = "EWRP"
Case "Bautino"
Me.ComboBox2.RowSource = "Bautino"
Case "Trunkline"
Me.ComboBox2.RowSource = "Trunkline"
Case Else
'do nothing
End Select
End Sub
Private Sub ComboBox2_Change()
End Sub
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
With Sheets = ComboBox1.Value.UsedRange 'worksheet with your data
.AutoFilter 'Clear previous filter if any
If ComboBox2.ListIndex > -1 Then .AutoFilter 1, ComboBox2.Value
End With
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton2_Click()
Unload Me
End SubFinally once the user has confirmed that the data contained in the particular row is correct I want to copy and paste to a word template. So far the code I have appears to work but I really need it to be dynamic, i.e. instead of using cells as shown below I want the use the row selected in the UserForm automatically, especially the images.
Code:
Sub CommandPrint1()
'
' CommandPrint_Click Macro
' Opens Word Template pastes BM data saves as new file name
'
UserForm1.Show
Dim WDApp As Word.Application 'opens word in background
Dim WDDoc As Word.Document
Set WDApp = New Word.Application
With WDApp
Set WDDoc = .Documents.Add(Template:="Survey Station Description 3.dotm")
.Visible = True 'line above opens the station description template this line makes it visible
.Selection.GoTo what:=wdGoToBookmark, Name:="Name"
.Selection.TypeText Text:=Range("A6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="Location"
.Selection.TypeText Text:=Range("B6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="Lat4326"
.Selection.TypeText Text:=Range("C6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="Long4326"
.Selection.TypeText Text:=Range("D6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="Lat1303v4284"
.Selection.TypeText Text:=Range("E6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="Long1303v4284"
.Selection.TypeText Text:=Range("F6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="E1303v28409"
.Selection.TypeText Text:=Range("G6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="N1303v28409"
.Selection.TypeText Text:=Range("H6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="Lat15865v4284"
.Selection.TypeText Text:=Range("I6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="Long15865v4284"
.Selection.TypeText Text:=Range("J6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="E15865v28409"
.Selection.TypeText Text:=Range("K6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="N15865v28409"
.Selection.TypeText Text:=Range("L6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="Elevation"
.Selection.TypeText Text:=Range("M6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="ScaleFactor"
.Selection.TypeText Text:=Range("N6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="LocalGridE"
.Selection.TypeText Text:=Range("O6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="LocalGridN"
.Selection.TypeText Text:=Range("P6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="txtPoint"
.Selection.TypeText Text:=Range("Q6").Value
.Selection.GoTo what:=wdGoToBookmark, Name:="txtNotes"
.Selection.TypeText Text:=Range("R6").Value
'code above copies all control point information from the excel sheet on row 6 and pastes it into the template but opens it in a new word document
'code below copies images and paste into the new word document so far only one image is used, this is test code
Worksheets("Sheet1").Shapes("Picture 6").Copy
.Selection.GoTo what:=wdGoToBookmark, Name:="Map"
.Selection.PasteSpecial Link:=False, DisplayAsIcon:=False, _
DataType:=wdPasteMetafilePicture ', Placement:=wdFloatOverText
End With
errorHandler:
Set WDApp = Nothing
Set myDoc = Nothing
End Sub
