Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!

The Chief Smeg

Members
  • Content count

    16
  • Joined

  • Last visited

Community Reputation

0 Neutral

About The Chief Smeg

  • Rank
    Newbie 1

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. The Chief Smeg

    Selecting ranges within ranges

    Ok I'd better explain this properly. I have a worksheet that has a space for the user to type a list, this area is fairly large so that the user can add a lot of items to this list. I know how to select the entire range of what we shall call the input area, but how do I use VBA to select only the entries made? The idea is that I have some code running that will loop through the list to perform aspecified function. Anybody got any ideas? (My looping code works already on a fixed data set btw)
  2. The Chief Smeg

    Error Trapping

    I have a number of VBA projects on the go and I was wondering if the collective have any advice regarding error trapping other than "have some"; although at the moment I don't hav it in any of my projects and was wondering where to start?
  3. The Chief Smeg

    Problem Referencing current worksheet

    It works! The formula: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) Only works on a saved worksheet, not the one being generated by my template spreadsheet. who knew! Cheers Caleeco
  4. The Chief Smeg

    Hiding Cells

    THAT is rather clever - didn't know about that little trick; one for my library!
  5. The Chief Smeg

    Problem Referencing current worksheet

    I think the problem may lie with the spreadsheet cell - this does not seem to work on mac (but does on windows): =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) The idea is it returns the name of the tab
  6. The Chief Smeg

    Problem Referencing current worksheet

    OK so we are still getting 'Run Time Error 13' 'Type Mismatch' even when I post it in the direction of a specific worksheet tab. The cell I am pointing at is a concatenation of a number of 2 cells from different worksheet tabs (done by a formula in the cell).
  7. The Chief Smeg

    Problem Referencing current worksheet

    Ok folks, I'm having some trouble again so I thought I would ask the collective to see if I can get an answer/solution: I have a spreadsheet with multiple tabs which are all identical apart from the content of certain cells. I want to use a single piece of VBA to create a PDF of the current tab only, but then use the same via code for the next tab and so on. I can create the PDF fine, my problem comes when debugging - I can see the fPath dim ok, but I cannot get fName to define the string value I have tried all sorts of different solutions with the attached code: Sub CreatePDFfile() 'Create and save PDF for current tab Dim fPath As String Dim fName As String fPath = Worksheets("Welcome").Range("D6").Value fName = Range(“V6”).Value Application.ScreenUpdating = False Range("B2:L58").Select ActiveSheet.PageSetup.PrintArea = "$B$2:$L$58" ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ fPath & fName, Quality:= _ xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=True Range("A1").Select Application.ScreenUpdating = True End Sub I am using Excel 2011 for Mac (which is the same VBA wise as Excel 2007 for windows i.e. VBA 6.5) Help!
  8. The Chief Smeg

    Macro to insert custom images

    I have FIXED IT! Firstly I had made a schoolboy error and left the worksheet protected (the range I was selecting was a cell I had designated as Locked. But never mind that here is the code I eventually got working: Sub AddSignature() 'Add Signature Images to Timesheet and Expenses Tabs in Workbook Application.ScreenUpdating = False Dim SigPath As String Dim tSigFile As String Dim eSigFile As String Dim tSig As String Dim eSig As String With Worksheets("Macro Variables") 'Read Input Variables (File Path & File Name) SigPath = .Range("D7").Value tSigFile = .Range("D10").Value eSigFile = .Range("D11").Value End With tSig = SigPath & tSigFile 'Concatenate File Path & File Names eSig = SigPath & eSigFile Worksheets("Timesheet").Range("G27:H27").Select 'Define range to place img ActiveSheet.Pictures.Insert (tSig) Worksheets("Expenses").Activate Worksheets("Expenses").Range("G57").Select ActiveSheet.Pictures.Insert (eSig) Range("A1").Select Application.ScreenUpdating = True End Sub And rather oddly, I needed to insert the statement to make the Expenses tab active before I could get the image file to insert itself - without that line in stopped in debug mode at the point where it was about to select the range G57. VBA on a Mac is indeed a little quirky. Thank you for all your help.
  9. The Chief Smeg

    Macro to insert custom images

    Been reading about this and a lot of people are having this issue and it seems there is no common answer. I'll record a macro (yes, sorry) and use that. It just means I cannot use the dim function and have it read variations from a worksheet tab. Oh well. Thanks for all your help Caleeco
  10. The Chief Smeg

    Macro to insert custom images

    Ok, tried that and the latest error message is 'Run-time error '1004' the specified value is out of range. And it a Falls over when we Set theShape. I am well and truly lost now. Perhaps a good sleep will help
  11. The Chief Smeg

    Macro to insert custom images

    Well, we are now seeing different error messages so I suppose that may be progress. Run-time errorv'91' Object variable or With block variable not set Now I think this is pointing to 'paste cell' because the locals window showed 'nothing' against both 'postCell' and 'Shape'. Stepping through the code, it fell over at 'pasteCell = Worksheets("Timesheet").Range("G27")' As for not affording a Mac, they are good and they are expensive, but they just work (except for with VBA in Office 2011 it would seem). I appreciate your help here Caleeco, Smeggy
  12. The Chief Smeg

    Macro to insert custom images

    As requested, D7 Macbook Pro HD:Users:USERNAME:Documents:Amos Engineering:Master Blank Files:Signature files: D10 SignatureT.png D11 SignatureE.png Where you see USERNAME against D7 then that is simply my real name with no spaces or Periods, the Colons are used instead of slashes because Mac uses a Unix architecture. If I use the following code: Sub AddSignature() ' ' AddSignature Macro ' Macro to Add Signature Images to Timesheet and Expenses Tabs in Workbook ' ' Keyboard Shortcut: Option+Cmd+y ' Application.ScreenUpdating = False Sheets("Timesheet").Select Range("G27:H27").Select ActiveSheet.Pictures.Insert("Macbook Pro HD:Users:USERNAME:Documents:Amos Engineering:Master Blank Files:Signature files:SignatureT.png").Select Range("A1").Select Sheets("Expenses").Select Range("G57").Select ActiveSheet.Pictures.Insert("Macbook Pro HD:Users:USERNAME:Documents:Amos Engineering:Master Blank Files:Signature files:SignatureE.png").Select Range("A1").Select Sheets("Invoice").Select Range("A1").Select Application.ScreenUpdating = True End Sub Then everything works perfectly, I would just like to be able to control the file path and name outside of the VBA code. Am I being too ambitious? I thought concatenating the path and filename would work - seems I was slightly wrong there. Thanks for you help Smeggy.
  13. The Chief Smeg

    Macro to insert custom images

    Sorry Caleeco - it still falls over with the same error message. I think I need to add I am using a Mac and office 2011 (which is VBA 6.5) it is falling over at ActiveSheet.Pictures.Insert (tSig) but i did manage to follow what you did
  14. The Chief Smeg

    Macro to insert custom images

    Just to add, it falls over as it tries to insert tsig
  15. The Chief Smeg

    Macro to insert custom images

    So I have a bit of VB that is not working so well and I wondered if the collective could help? I am trying to add an image file using a Dim as the filename Option Explicit Sub AddSignature() ' ' AddSignature Macro ' Macro to Add Signature Images to Timesheet and Expenses Tabs in Workbook Dim SigPath As String Dim tSigFile As String Dim eSigFile As String Dim tSig As String Dim eSig As String SigPath = Worksheets("Macro Variables").Range("D7").Value tSigFile = Worksheets("Macro Variables").Range("D10").Value eSigFile = Worksheets("Macro Variables").Range("D11").Value tSig = SigPath & tSigFile eSig = SigPath & eSigFile Application.ScreenUpdating = False Sheets("Timesheet").Select Range("G27:H27").Select ActiveSheet.Pictures.Insert(tSig).Select Range("A1").Select Sheets("Expenses").Select Range("G57").Select ActiveSheet.Pictures.Insert(eSig).Select Range("A1").Select Sheets("Invoice").Select Range("A1").Select Application.ScreenUpdating = True End Sub And I am getting the following error when trying to run "Unable to get the inner property of the Pictures class" (oh and it is a run-time error 1004 - but you already guessed that bit). Can anyone tell me what I have done wrong please? I have a screen grab that I can email/pm if required, but essentially the locals window correctly reads all the Dim and String info. Help!
×