Hyside2's Profile
Top Contributor
601
Points

Questions
0

Answers
9

  • Top Contributor Asked 6 days ago in VBA: General.

    Here is what I came up with.  The macro will color the text in the cell white or black depending on the status of the  check box.

    Each check box is assigned to the same macro. Each check box has a cell link to the location is it in. Example check box 1 has a cell link in cell “A1” and so on.

    RE: Excel-Hiding rows with specific text and multiple textboxes

    Here is the code. Hope this works for you.

    Sub hid()

    Dim BeginRow As Integer
    Dim EndRow As Integer
    Dim ChkCol As Integer
    Dim RowCnt As Integer
    Dim r As Integer
    Dim box As Integer

    BeginRow = 10
    EndRow = 205
    ChkCol = 2
    r = 1

    For box = 1 To 5 ‘ this represents the number of check boxes being used
    For RowCnt = BeginRow To EndRow

    If Cells(r, 1) = True Then
    If Cells(RowCnt, ChkCol).Value = Cells(r, 2) Then
    Cells(RowCnt, ChkCol).Font.Color = RGB(255, 255, 255)
    End If
    End If

    If Cells(r, 1) <> True Then
    If Cells(RowCnt, ChkCol).Value = Cells(r, 2) Then
    Cells(RowCnt, ChkCol).Font.Color = RGB(0, 0, 0)
    End If

    End If
    Next RowCnt

    r = r + 1
    Next box
    End Sub

    • 21 views
    • 1 answers
    • 0 votes
  • Top Contributor Asked on January 3, 2018 in VBA: Macros.

    Please try this as well.

    Sub Save2PDF()

    Dim Fname As String

    Fname = InputBox(“Enter file name to save”, “Save as PDF”)

    ActiveWorkbook.Save

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=Fname, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

    End Sub

    • 20 views
    • 2 answers
    • 0 votes
  • Top Contributor Asked on January 3, 2018 in VBA: Macros.

    Here is a simple copy and paste from cell A1 to D1 using vba

    Sub cpy()
    Range(“A1”).Copy Destination:=Range(“C1”)
    End Sub

    • 14 views
    • 1 answers
    • 0 votes
  • Top Contributor Asked on January 3, 2018 in VBA: Macros.

    I did not write this code but I think that it may help you. For more information about this code visit :  http://www.contextures.com/excelvbapdf.html

    Sub PDFActiveSheet()
    ‘www.contextures.com
    ‘for Excel 2010 and later
    Dim wsA As Worksheet
    Dim wbA As Workbook
    Dim strTime As String
    Dim strName As String
    Dim strPath As String
    Dim strFile As String
    Dim strPathFile As String
    Dim myFile As Variant
    On Error GoTo errHandler

    Set wbA = ActiveWorkbook
    Set wsA = ActiveSheet
    strTime = Format(Now(), “yyyymmdd\_hhmm”)

    ‘get active workbook folder, if saved
    strPath = wbA.Path
    If strPath = “” Then
    strPath = Application.DefaultFilePath
    End If
    strPath = strPath & “\”

    ‘replace spaces and periods in sheet name
    strName = Replace(wsA.Name, ” “, “”)
    strName = Replace(strName, “.”, “_”)

    ‘create default name for savng file
    strFile = strName & “_” & strTime & “.pdf”
    strPathFile = strPath & strFile

    ‘use can enter name and
    ‘ select folder for file
    myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
    FileFilter:=”PDF Files (*.pdf), *.pdf”, _
    Title:=”Select Folder and FileName to save”)

    ‘export to PDF if a folder was selected
    If myFile <> “False” Then
    wsA.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=myFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
    ‘confirmation message with file info
    MsgBox “PDF file has been created: ” _
    & vbCrLf _
    & myFile
    End If

    exitHandler:
    Exit Sub
    errHandler:
    MsgBox “Could not create PDF file”
    Resume exitHandler
    End Sub

    • 20 views
    • 2 answers
    • 0 votes
  • Top Contributor Asked on December 30, 2017 in VBA: Macros.

    Not sure if this will help. When you save your file click Tools>General Options and then click on Always create backup.  No macro needed to keep a backup of your work.

    Save a backup fileSave a backup file

    • 28 views
    • 1 answers
    • 0 votes
  • Top Contributor Asked on December 30, 2017 in VBA: Macros.

    Sheet1 column “A”  must have at least 2 rows (Cells A1 and A2) of data for macro to work. Less than that will msgbox an error.

    Sheet 2 contains a Table1 located in ” A1″  with a header of  4 columns and 2 rows including the header row.

    Data on sheet1 columns “A” and “B” will be moved to Sheet2 Table1 columns “C” and “D”.

    Sheet1 will also be cleaned of data in columns “A” and “B”.

    Good Luck!

    Sub copycols2table()
    Dim x As Integer
    Dim c As Integer
    Dim n As Integer
    Dim r As Integer
    Dim rc As Integer
    Dim ws2 As Worksheet

    x = 1
    c = 3
    n = 2
    r = 1
    rc = 1
    Set ws2 = Worksheets(“Sheet2”)

    For i = 1 To 2
    If IsEmpty(Cells(r, rc)) Then
    MsgBox “Please enter minimum of 2 rows of data on Sheet1 columns A.”
    Exit Sub

    End If
    r = r + 1
    Next i
    r = 1
    rc = 2
    If IsEmpty(Cells(r, rc)) Then
    n = 1
    End If

    For i = 1 To n
    With ws2
    Range(Cells(1, x), Cells(1, x).End(xlDown)).Copy _
    Destination:=ws2.Cells(1, c).End(xlDown).Offset(1)
    End With
    x = 2
    c = 4
    Next i

    Range(“A1”).CurrentRegion.ClearContents
    End Sub

    • 20 views
    • 1 answers
    • 0 votes
  • Top Contributor Asked on December 30, 2017 in VBA: Macros.

    This will delete columns 3 and 4 of Table 1 on Sheet 1.

    Sub delcol3_4()
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects(“Table1”)

    For i = 1 To 2
    With Worksheets(“Sheet1”)
    tbl.ListColumns(3).Delete
    End With
    Next i

    End Sub

    • 37 views
    • 3 answers
    • 0 votes
  • Top Contributor Asked on February 13, 2017 in VBA: Programming.

    hi, give this a try.  Cell A1 will contain 120105, Cell G2 will contain the answer 1215

    Sub delzero()
     '
     ' delzero Macro
     ' Excel 2010
     ' in cell "A1" enter 120105 - run macro
     ' cell G2 will contain the answer 1215
     ' this is designed around a 6 digit number
    [a1].Select
     Application.Width = 928.5
     Application.Height = 753.75
     Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
     FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array _
     (5, 1), Array(6, 1)), TrailingMinusNumbers:=True
    Do Until IsEmpty(ActiveCell)
     If ActiveCell.Value = "0" Then
     ActiveCell.Delete
     Else
     ActiveCell.Offset(0, 1).Select
     End If
     Loop
     [g2].Select
     ActiveCell.Formula = "=Concatenate(A1,B1,C1,D1,E1,F1)"
     With Range("G2")
     .Value = .Value
     End With
    End Sub
    
    • 111 views
    • 1 answers
    • 0 votes
  • Top Contributor Asked on January 29, 2017 in VBA: General.

    Here is how I did it.

     

     
    Sub pastedifferentsheets()
     ' excel 2010
     ' paste Columns in different sheets
    Dim lRow As Integer
     Dim i As Integer
     lRow = [sheet1!H1] ' Sheet1 H1 (=Count(A:A)) so column A must contain contiguous data
     i = 1
    Application.ScreenUpdating = False
     [a1].Select
    Do Until i > lRow
     If ActiveCell.Value = "Compatible" Then
     ActiveCell.Offset(0, 1).Select
     If ActiveCell.Value = "Pass" Then
     ActiveCell.Offset(0, 1).Copy
     Sheets("Sheet2").Select
     [D1].Select
     If ActiveCell.Value <> "" Then
     ActiveCell.Offset(1, 0).Select
     If ActiveCell.Value <> "" Then
     ActiveCell.Offset(-1, 0).Select
     ActiveCell.End(xlDown).Select
     ActiveCell.Offset(1, 0).Select
     End If
    End If
     ActiveSheet.Paste
     Sheets("Sheet1").Select
    End If
     ActiveCell.Offset(0, -1).Select
     End If
     ActiveCell.Offset(1, 0).Select
    i = i + 1
     Loop
     Application.CutCopyMode = False 'gets rid of rubberband lines
     ActiveWorkbook.Save ' this will save your work
     End Sub
    
    • 155 views
    • 7 answers
    • 0 votes