ComboBox1 Value is a Sheet Name, how to open that sheet? Also go to value in Combobox2 &.......

[southcraven]

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

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 Sub

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.

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

 

[southcraven]

Paste Picture from Excel into Word Error 438

Select Image In Excel Cell and paste to Word Doc Error 438