Posts by Mr_Mod

    I have the below code that i have set up to create a header, footer and manipulate some other bits on a page, this works all fine. But what I now want to do is apply this to all the sheets in the workbook at the press of a single button.
    I have a separate module that with a button placed on what i call the cover page which allows me to print all active pages, so i would like to use this button to apply the headers/footers etc.
    I cant figure out how to do this, any ideas?


    [VBA]
    Sub AddFooter()
    '
    ' Adds Filename, Worksheet Name and Date (static or updated type) as Footer
    '
    Dim Response As Integer
    Dim lenheader As String
    Dim lenfooter As String
    Dim wsSheet As Worksheet



    Application.ScreenUpdating = False


    ' get workbook name and delete extension
    Filename = ActiveWorkbook.Name
    If InStr(Filename, ".") > 0 Then
    Filename = Left(Filename, InStr(Filename, ".") - 1)
    End If


    'clear header & footer
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""


    End With


    'get worksheet name
    With ActiveSheet.PageSetup
    If InStr(Filename, ".") > 0 Then
    Filename = Left(Filename, InStr(Filename, ".") - 1)
    End If


    'remove text config file
    Sheets("Cover").Range("c5").Copy Destination:=Sheets("Smartpack-S").Range("f3")
    Sheets("Smartpack-S").Range("f3").Replace what:="Config File", Replacement:=" - V2A", LookAt:=xlPart, MatchCase:=False
    Sheets("Cover").Range("c5").Copy Destination:=Sheets("Smartpack-S").Range("f5")
    Sheets("Smartpack-S").Range("f5").Replace what:="Config File", Replacement:=" - V2A", LookAt:=xlPart, MatchCase:=False


    'change row colour
    'Range("f3:f5").Interior.Color = RGB(255, 255, 204)


    'row height
    Rows("3:1").RowHeight = 19
    Rows("5:1").RowHeight = 19


    'what goes into the header and footers
    .RightHeader = "&B&16" & Sheets("Smartpack-S").Range("f3 ").Value & Chr(10) & "&16&A" & Chr(10) '" Settings"


    Filename = ActiveWorkbook.Name
    If InStr(Filename, "_") > 0 Then
    Filename = Left(Filename, InStr(Filename, "_") - 1)
    End If
    .LeftFooter = Chr(10) & Chr(10) & Chr(10) & Chr(10) & "&B&16" & "Drawing Number: " & Filename
    .RightFooter = Chr(10) & "&B&16" & "&d" & Chr(10) & "Page :" & " " & "&P" & " of " & "&N"


    'add logo to left header
    With ActiveSheet.PageSetup.LeftHeaderPicture
    .Filename = "D:\logo.jpg"
    .Height = 30
    '.Width = 400 .Brightness = 0.36
    '.ColorType = msoPictureGrayscale
    '.Contrast = 0.39
    End With

    ActiveSheet.PageSetup.LeftHeader = "&G"



    End With


    Application.ScreenUpdating = True

    End Sub
    [/VBA]



    Print module


    [VBA]
    Sub PrintAll()


    CurrentSheet = ActiveSheet.Name

    'If fleximonitor sheet is showing
    If Sheets("fleximonitor").Visible = True Then
    Sheets(Array("Cover", "Description", "Smartpack-S", "fleximonitor")).PrintPreview 'Print only 4 sheets
    'If only 4 sheets are showing
    Else 'If Sheets("fleximonitor").Hidden = True Then
    Sheets(Array("Cover", "Description", "Smartpack-S")).PrintPreview 'Print only 3 sheets

    End If

    End Sub
    [/VBA]

    Hi All,
    I am looking at a way to use a handheld bar code scanner and input the data into a worksheet.
    What i need to do is the following
    1: Scan cabinet serial number into cell C3 (9 characters in total for serial number)
    2: Scan module 1 serial number into Cell D3 (12 characters in total for serial number)
    3: Scan module 2 serial number into Cell E3 (12 characters in total for serial number)
    each time the serial number is scanned i want to automatically jump to the next column and enter the serial number and jump to next column


    once i have those 3 items scanned i then want to jump down to the next row and continue doing the same.


    I know there has to be some VBA code here but i have no clue where to start.


    Can someone help me out please

    I have tried searching but have not found what i am really looking for.


    I have a workbook with 6 worksheets containg data named Sheet 1 to Sheet 6, and a sheet named totals which is where i wish to save data from the other sheets.
    Each of the sheets 1 through to 6 have data with a name of product in column F, price in column G, and in column B is whether the product is obsolete.


    What i wish to do is on sheet "total" is if on any of the other sheets has obsolete next to the product is to list them down the sheet in sequential order down the sheet, once sheet 1 is scanned and checked it moves to the next sheet until all the sheets 1 to 6 have been checked and the data appears on the totals sheet.
    Against each row i wish to also include the sheet name where the obsoleted item appears.


    Is this best done in a formular or VBA, i have basically given up trying "if" and "lookups"



    any assistance appreciated

    Re: Saving column data to files


    Now i am confused, if i have the VB Editor open it will bomb out at tf.write strtext however close it and it works ok except i need to add a carrige return after each row of data as they are all in a single line and not as per the data which is from E6 to E133.

    Re: Saving column data to files


    Im progressing with this, however i get an error Invalid procedure call or argument (Error 5) with the sub thats writes the file at the line that says "tf.Write strtext"
    What am i doing wrong


    Re: Saving column data to files


    Many thanks for your help, im just a novice but if i call the above sub routine i get an error "Compile error - Argument not optional". I am just using "GetTheData" without any references, not sure how the variables get carried across to this sub routine. It might be my rusty brain


    Re: Create folder in My Data Sources


    was just the wayway the folder structure went was reversed

    Code
    If Not fs.FolderExists(StartPath & LastPath & MiddlePath & EndPath & newfol) Then
                If Not fs.FolderExists(StartPath & LastPath) Then MkDir (StartPath & LastPath)
                If Not fs.FolderExists(StartPath & LastPath & MiddlePath) Then MkDir (StartPath & LastPath & MiddlePath)
                If Not fs.FolderExists(StartPath & LastPath & MiddlePath & EndPath) Then MkDir (StartPath & LastPath & MiddlePath & EndPath)
                If Not fs.FolderExists(StartPath & LastPath & MiddlePath & EndPath & newfol) Then MkDir (StartPath & LastPath & MiddlePath & EndPath & newfol)


    Many thanks for your help

    I have a workbook containing a number of sheets (100+), i have created a form where you select the relevant component. By pressing a button on the form, this will create a folder for the component and will allow 2 files to be created one called "preset.hex" the other "name.txt" these will be created for the following.


    Column "A" rows 6 to 133 becomes "name.txt"
    Column "G" rows 6 to 133 becomes "preset.hex" however depending on what option button selected this could be Column "F" or Columns "H thru to J"


    not all rows in Column "A" are populated where all the rows in the other columns are


    What i wish to do is create these 2 files and if it finds a null in column "A" it moves to next row and does not save the data in the other columns on the corresponding row if row "A" is blank.
    The relevent sheet is determind by the form
    I have searched the web for solutions but cannot find a method that would do what im looking for

    Re: Create folder in My Data Sources


    Tried your variation on the code and it does not seem to be working correctly. It doesnt create the folder nor display anything in the label box. If i delete the "if Not " part it works


    Code
    If Not Dir(StartPath) = vbNullString Then              'check to see if path is same as file path
     ChDir FilePath                                  'if not then change to filepath
     If Dir(FilePath) = vbNullString Then
     MkDir (myPath) & (newfol)                   'file path correct so create folder             
    FileNameLabel.Caption = FilePath & newfol   'display path in label         
    End If      
    End If


    Now i assume that if the directory structure does not exist then additional code would be required

    Re: Create folder in My Data Sources


    Many thanks, solved my first problem.
    Will put the code here incase anyone else wants to do something similar

    Hi,
    I have the following code which successfully creates a folder in "C:\Users\admin\Documents\My Data Sources\", now i know not everyone uses admin as their login name for windows, how can you determine their user name so that the folder can be created in their My Data Sources Folder?


    I am trying to create a look up where the result of a value stored in cell H3 is checked to so if its value is equal to something then the result is placed into Z3, what i have written is to large as it has more than 64 levels of nesting in the look up.
    Ideally a bit of VBA would be better but i have no clue how to even go about it.


    Code
    =IF(H3="0","1C",IF(H3="1","10",IF(H3="2","12",IF(H3="3","1D",IF(H3="4","0",IF(H3="5","1E",IF(H3="6","17",IF(H3="7","13",IF(H3="8","19",IF(H3="9","15",IF(H3="0A","16",IF(H3="0B","14",IF(H3="0C","40",IF(H3="0D","41",IF(H3="0E","48",IF(H3="0F","47",IF(H3="10","42",IF(H3="11","44",IF(H3="12","43",IF(H3="13","26",IF(H3="14","1",IF(H3="15","35",IF(H3="16","24",IF(H3="17","2",IF(H3="18","0F",IF(H3="19","3",IF(H3="1A","4",IF(H3="1B","0E",IF(H3="1C","49",IF(H3="1D","4A",IF(H3="1E","1F",IF(H3="1F","5",IF(H3="20","27",IF(H3="21","36",IF(H3="22","39",IF(H3="23","3A",IF(H3="24","4D",IF(H3="25","2C",IF(H3="26","20",IF(H3="27","2A",IF(H3="28","37",IF(H3="29","53",IF(H3="2A","1B",IF(H3="2B","4B",IF(H3="2C","55",IF(H3="2D","2F",IF(H3="2E","21",IF(H3="2F","25",IF(H3="30","38",IF(H3="31","18",IF(H3="32","11",IF(H3="33","2D",IF(H3="34","2E",IF(H3="35","4F",IF(H3="36","50",IF(H3="37","4E",IF(H3="38","54",IF(H3="39","45",IF(H3="3A","2B",IF(H3="3B","4C",IF(H3="3C","51",IF(H3="3D","30",IF(H3="3E","22",IF(H3="3F","56",IF(H3="40","57",IF(H3="41","6",IF(H3="42","7",IF(H3="43","8",IF(H3="44","9",IF(H3="45","31",IF(H3="46","23",IF(H3="47","0A",IF(H3="48","0B",IF(H3="49","0C",IF(H3="4A","0D",IF(H3="4B","28",IF(H3="4C","29",IF(H3="4D","33",IF(H3="4E","34",IF(H3="4F","52",IF(H3="50","32",IF(H3="51","46",IF(H3="52","3B",IF(H3="53","3C",IF(H3="54","1A",IF(H3="55","3D",IF(H3="56","3E",IF(H3="57","3F",IF(H3="58","74",IF(H3="59","75",IF(H3="5A","76",IF(H3="5B","77",IF(H3="5C","70",IF(H3="5D","71",IF(H3="5E","72",IF(H3="5F","73",IF(H3="60","7C",IF(H3="61","7D",IF(H3="62","7E",IF(H3="63","7F",IF(H3="64","78",IF(H3="65","79",IF(H3="66","7A",IF(H3="67","7B",IF(H3="68","80",IF(H3="69","81",IF(H3="6A","82",IF(H3="6B","83",IF(H3="6C","88",IF(H3="6D","89",IF(H3="6E","8A",IF(H3="6F","8B",IF(H3="70","58",IF(H3="71","59",IF(H3="72","5A",IF(H3="73","5B",IF(H3="74","62",IF(H3="75","63",IF(H3="76","64",IF(H3="77","65",IF(H3="78","5C",IF(H3="79","60",IF(H3="7A","5D",IF(H3="7B","5E",IF(H3="7C","5F",IF(H3="7D","66",IF(H3="7E","67",IF(H3="7F","68",


    Thanks in advance

    Hi,
    I have an ever growing workbook which has approx 60+ worksheets with in it. I have used the following code from this site to generater an index of each of the "tab's" names, not being prolifient with VBA i would like to be able to append the index page to include the "tab" name and the title which appears in each work sheet at cell "E2" and in cell "B2". The value in "E2" is required to be in column "B" adjacent to the corresponding "tab" name that is in Column "A". Value in "B2" would be in the corresponding Column "C"
    Would someone be able to give me some pointers in how i would go about this, i have played around adjusting this code and just cant get the value in "B2" to appear in sequence other than it is in cell C1 but over written each time.


    Cheers
    Guy
    original cade from this site