PDA

View Full Version : Excel VBA Queries



shah
11-17-2016, 08:25 AM
Hi,

I have been working on a project and needed to include VBA codes to close the original file, copy it and save it in a different location with a specific naming format. I have achieved this but now need to insert more codes to firstly identify blank rows in a given range and then delete them (there are 3 different ranges I want to consider being cells B18 to B25, B27 to B31 and B33 to B38). I also need to include an image based on what text is in cell C7 (which is in a form of a dropdown). I have been trying many different things over the last week but haven't been successful. Please if someone can help me in this i would be really greatful. Thank you in advance! So far, the code I have is the following:

Sub CommandButton1_Click()


ActiveSheet.Unprotect Password:="Password1"

ActiveSheet.Copy


Dim SaveName As String
SaveName = ActiveSheet.Range("C5").Text

ActiveSheet.Protect Password:="OperationHUMP"


With ActiveWorkbook

.Worksheets("Media Plan").CommandButton1.Visible = False
.SaveAs "S:\" & SaveName & ".xls"
End With

Workbooks.Open ("S:\" & SaveName & ".xls")
Workbooks("Media Plan.xlsb").Close False
End Sub

onlyadrafter
11-19-2016, 09:00 AM
Hello,

here is the code to delete rows based on blanks in Col B.


Sub delete_rows_from_b() For MY_ROWS = 38 To 18 Step -1
If IsEmpty(Range("B" & MY_ROWS).Value) And _
MY_ROWS <> 32 And MY_ROWS <> 26 Then
Rows(MY_ROWS).Delete
End If
Next MY_ROWS
End Sub

Suggest you run the macro recorder to insert the image or provide some further information.

KevO
11-22-2016, 04:20 AM
This adds an image from the folder of your choice based on value in cell C7
This code assumes
- that the name does not contain the extension
- that the image is a jpeg
You will need to amend
- the ImagePath (to match the path to your image files)
- the extension (if not a jpeg)
- left,top, width and height values (to locate and size the image)


Sub AddImage()
Dim ImagePath As String, ImageName As String, FullPath As String
Set MySheet = ActiveSheet

ImagePath = "C:\Documents\" '<<< AMEND
ImageName = MySheet.Range("C7").Value & ".jpg" '<<< AMEND
FullPath = ImagePath & ImageName

'add image to location
MySheet.Shapes.AddPicture FullPath, True, True, 100, 100, 70, 70 '<<< AMEND
'= SHEETNAME.Shapes.AddPicture(Filename, LinkToFile, SaveWithDocument, Left, Top, Width, Height)
End Sub