Use Value from inputbox as range

[somers]

Hi,
I'm trying to use the input from the user (Range or number that i can offset from a fixed cell).

The error returned is:

I Highlight the code where it goes wrong.
If i use Range("C16") it works
but if i use the variable (as range) afbnr i get the error.

What am i doing wrong here?

Sub Uploadafbeelding1_C16()


    Dim afbnr As Range
      Set afbnr = Application.InputBox(prompt:="Selecteer cel voor afbeelding", Type:=8)
        If afbnr Is Nothing Then Exit Sub

    
    Dim profile As String
    On Error GoTo 0
    Dim fd As FileDialog

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Clear
        .Filters.Add "Picture Files", "*.bmp;*.jpg;*.gif;*.png"
        .ButtonName = "Select"
        .AllowMultiSelect = False
        .Title = "Selecteer afbeelding"
        .InitialView = msoFileDialogViewDetails
 
    If .Show = -1 Then
    
      Dim img As Shape
      Dim nH
      Dim nW
      Dim nPix
      Dim x
    
          Set img = ActiveSheet.Shapes.AddPicture _
                     (Filename:=fd.SelectedItems(1), _
                      LinkToFile:=msoFalse, _
                      SaveWithDocument:=msoCTrue, _
                      Left:=ActiveSheet.[B][COLOR=rgb(184, 49, 47)]afbnr[/COLOR][/B].Left + 2, _
                      Top:=ActiveSheet.Range("C16").Top + 2, _
                      Width:=-1, _
                      Height:=-1)
                      
               With img
                 .LockAspectRatio = msoTrue
               '  .Width = 38
                 .Height = 58
               End With
    
              With Selection
                    nH = img.Height + 4
                   ' nW = img.Width + 4
                    Range("C16").RowHeight = nH
                    'Range("C16").Rowwidth = nH
              End With
        
        Else
          MsgBox ("Geannuleerd.")
        End If
    
        End With
End Sub

 

[6StringJazzer]

afbnr is a Range so your syntax is wrong here:

ActiveSheet.afbnr.Left

(You cannot apply formatting inside VBA code tags. To apply formatting, use Rich code tags.)

Try this

afbnr.Left

That should work but if that references the wrong sheet then this to force the ActiveSheet reference:

ActiveSheet.Range(afbnr.Address).Left

 

[somers]

Now I want to add the selected picture to the same cell in two worksheets with the same row/column height & width adjustions.

I have folowing code, it works perfect in sheet 1 but in sheet 2 the picture is randomly pasted.

Sub Uploadafbeelding1_C16()


    Dim afbnr As Range
      Set afbnr = Application.InputBox(prompt:="Selecteer cel voor afbeelding", Type:=8)
       If afbnr Is Nothing Then Exit Sub
       
afbnr.Copy
    
    On Error GoTo 0
    Dim fd As FileDialog

    

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Clear
        .Filters.Add "Picture Files", "*.bmp;*.jpg;*.gif;*.png"
        .ButtonName = "Select"
        .AllowMultiSelect = False
        .Title = "Selecteer afbeelding"
        .InitialView = msoFileDialogViewDetails
     If .Show = -1 Then


      Dim img As Shape
      Dim nH
      Dim nW
      Dim nPix
      Dim x
      Dim I As Integer
      
For I = 1 To 2

          Set img = Worksheets(I).Shapes.AddPicture _
                     (Filename:=fd.SelectedItems(1), _
                      LinkToFile:=msoFalse, _
                      SaveWithDocument:=msoCTrue, _
                      Left:=afbnr.Left + 2, _
                      Top:=afbnr.Top + 2, _
                      Width:=-1, _
                      Height:=-1)
                                            
               With img
                 .LockAspectRatio = msoTrue
               '  .Width = 38
                 .Height = 58
               End With
    
              With Selection
                    nH = img.Height + 4
                   ' nW = img.Width + 4
                    afbnr.RowHeight = nH
                    'afbnr.Rowwidth = nH
              End With
              
Next I

        Else
          MsgBox ("Geannuleerd.")
          
                End If
           End With
End Sub

How do i make the range and row/column adjustments the same as in sheet 2 as in sheet 1?