Microsoft Office

58K

Solutions

124

Articles & Videos

39K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Share tech news, updates, or what's on your mind.

Sign up to Post

need to clean Data  and format to beging againg
with new DATA
29039894b--1-.xlsm
0
[Webinar] Learn How Hackers Steal Your Credentials
LVL 8
[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Respected All,

I have Recorded a Macro to save a file  Please kindly Guide me i want name would be dynamic as if File Name is "CPR as at June 15, 2017-GRW" then it can save with concatenation of today Date 24-06-2017 eg. "CPR as at June 15, 2017-GRW-24-06-2017".

Please help and advice.

Regards Farhan

Sub Macro1()
'
' Macro1 Macro
'

'
    ChDir "C:\Users\Khalid Mahmood\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Khalid Mahmood\Desktop\CPR as at June 15, 2017-GRW-24.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Delete
End Sub
0
Hello,

I have a database (attached) that create worksheets based off the amount of work items in column B of the "summary" datasheet. The macro works great, except for that if the user clicks the button again after running the macro and runs the macro a second time, a new set of worksheets are created with the same name except with the time of the rerun also in the name. I was wondering if it would be possible to make it so that when the button is clicked a second time and the user chooses to create worksheets again that the worksheets are just updated? For example if a new row was added after creating worksheets and the macro was run again then just that new row would show up and all the other worksheets would just stay the same. Please let me know if I need to explain myself more clearly!

Thanks!

here is the code:

Sub CreateWorkItems()
    Dim WS As Worksheet
    Dim WSItem As Worksheet
    Dim WSTemp As Worksheet
    Dim MaxRow As Long, I As Long
    Dim sClientId As String, sJobId As String, sWItem As String

    '---> Disable Events
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False


        '---> Set Variables
        Set WS = ActiveSheet
        MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
        Set WSTemp = Sheets("template")
        sClientId = WS.Range("B1")
        sJobId = WS.Range("B2")

        If MaxRow >= 9 Then
            For I = 9 To MaxRow
        …
0
Hello!

I have a database (attached) and I want to be able to export the first two sheets to a PDF. However, the code I have now (below) which runs after clicking the Print Bid button, only converts part of the first worksheet. When the user is using this database there could potentially be many more work items and therefore I need the code to convert everything in all the columns to a PDF, except column C. That is the next problem, the Jump To column, column C, in the first worksheet should not be visible in the PDF but right now, despite the code, it is. If anyone has any suggestions on how to change these two things that would be wonderful! Thank you!

Sub PrintToPDF()
    Dim oWs As Worksheet
    Sheets("summary").Columns("C:C").EntireColumn.Hidden = True
    Set oWs = Sheets("summary")
    With oWs.PageSetup
        .FitToPagesWide = 1    ''/// prints to one page
    End With

    oWs.Columns("C:C").EntireColumn.Hidden = False

    With Sheets("service rates").PageSetup
        .FitToPagesWide = 1    ''/// prints to one page
    End With
    ''///Save the Array of worksheets (which will be selected) as a PDF
    ThisWorkbook.Sheets(Array("summary", "service rates")).Select
    On Error Resume Next
    oWs.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:="Test.pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            …
0
I have this solution
need to improve with vba the count

add an countig to the column A in count sheet

num3.png

also Need add
blank column in sheet PAIR  between CD, DE
num3a.pngnum3data.xlsm
0
Is there any prohibition against installing Office 365 Personal on a computer used at a business?

Same question about Office 2016 Home and Student
0
I've been trying to reset the colors in all boxes (RangeName = "Boxcolors") to yellow by writing a Macro.  Unfortunately, my VBA skills are not that good.  If you're up for it, take a look at the Macro for resetting the boxes to yellow.  It's a small macro that requires a tweek.

Much thanks in advance,

B.
C--Data-X-Data-Trash-Clear-Map.xlsm
0
EE Pros,

I have some merged cells that I'd like to be able to double-click and have the color in the box change to Yellow, Red and Green.

Is there an easy way to have a macro change the box color when double-clicked?

Much thanks in advance,

B.
C--Data-Dev-Mapping-Strategy-AddCol.xlsm
0
Hello Experts,

Just need confirmation. If I create a database application in MS Access 2010  can I open later in MS Access 2013 with all functionalities? Our organization is moving over to Office 2013 in a few months and I just want to make certain that all the applications that I've created in MS Access 2010 will still work in Access 2013.

Thanks!
0
I have Mac version 15.35 and El Capitan 10.11.6

If I have two instances of Word open, how can I use my keyboard to toggle between instances?

Thanks
0
Industry Leaders: We Want Your Opinion!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

I have a bunch of images that need to be uniform in size. What is the quickest way?

I use Word for Mac 15.35


Thanks
0
I use the latest Mac version Word...just downloaded.

And I see a few words underlined...link in the image I attached.

What does it mean?

Thanks

Underlined
0
Hello,

This question is in follow-up to another recent thread located here:

Modify formatting of selected intracellular text

In that thread, a VBA solution was provided to alter the formatting of certain portions of an Excel text entry while leaving the remaining portions unchanged. That solution (as far as I can determine with my limited knowledge of VBA) operates by including, within the code, the literal strings of text (ie the specific words) which are to be reformatted.

The follow-up question now is:

Can the VBA code described above be modified so that in place of the strings of text (which need to be reformatted), it contains variables which obtain those strings from say, an adjacent column in the spreadsheet?

I'm attaching a file that illustrates two possible options for the functionality I've got in mind. The first option (in the sheet tab labeled Text_Strings) is shown in the first screenshot below. The second option (in the sheet tab labeled MID_Function) is shown in the second screenshot below.

Note: I believe the second option (MID_Function), if it is doable, is a much better way to go than the first since it allows for a more precise designation of the text strings to be reformatted and also eliminates potential problems due to duplicate or repeated strings.

Option #1 Text_Strings
0
Hello!

I have created a button that I want to allow the user to be able to print just certain sheets of the workbook. I am wanting to create a macro for the button that prints columns A-J, but NOT column C, from the worksheet "summary". Then I would also like the worksheet called "service rates" to print right after the "summary" sheet. It would also be great if both sheets fit to the page so that they aren't being cut off!

Thanks so much :)
0
i have this solution but need to improve with vba
to count the triples from 4 numbers
num4triple.xlsm
0
I have been attempting to use this Macro that I found through VBA Express.  The Macro successfully disables the ability to right-click cut/copy/paste but the keyboard shortcuts are still active.  Any help is greatly appreciated.  I have very little knowledge about writing Macros and usually go to Google to find already existing codes to use.  Please see below:

'*** In a standard module ***
Option Explicit
 
Sub ToggleCutCopyAndPaste(Allow As Boolean)
     'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, Allow) ' cut
    Call EnableMenuItem(19, Allow) ' copy
    Call EnableMenuItem(22, Allow) ' paste
    Call EnableMenuItem(755, Allow) ' pastespecial
     
     'Activate/deactivate drag and drop ability
    Application.CellDragAndDrop = Allow
     
     'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
    With Application
        Select Case Allow
        Case Is = False
            .OnKey "^c", "CutCopyPasteDisabled"
            .OnKey "^v", "CutCopyPasteDisabled"
            .OnKey "^x", "CutCopyPasteDisabled"
            .OnKey "+{DEL}", "CutCopyPasteDisabled"
            .OnKey "^{INSERT}", "CutCopyPasteDisabled"
        Case Is = True
            .OnKey "^c"
            .OnKey "^v"
            .OnKey "^x"
            .OnKey "+{DEL}"
            .OnKey "^{INSERT}"
        End Select
    End With
End Sub
 
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
     …
0
I had this question after viewing Produce the count value next to each filterable item in a spreadsheet.

I am using this macro with data from another sheet and the results are cut-off due to all the data not fitting in dialog.

If possible, the results dialog should automatically expand as necessary to fit all the text to the right, and be in a smaller font size.
CountItemsv2.xlsm
0
Dear macro experts, please help.
I would like the macro to adjust (shrink or increase) row height anywhere in sheet depends of the text in cells. The rows and columns have Merged Cells and Wrapped Text. The text is always in other two rows and in same 11 column from B to L. Column length is static. That two rows need to be same size and the text need to fit.
Adjust.xlsx
0
Experts:

I have come across a wonderful (ready-to-use) Excel sample file that allows multiple values to be selected (in single cell) via a drop-down box.   I found the example at:  https://trumpexcel.com/select-multiple-items-drop-down-list-excel/

I would like to modify the code provided by Sumit Bansal.   Specifically, I need multiple drop-down (i.e., for every record in an XLS, I need the drop-down for assigning POCs).

Sumit's code below allows to use the drop-down for cell C2 only though (line: "If Target.Address = "$C$2" Then").   For testing purposes, I tried modifying the code with only two cells.   The proposed VBA "If Target.Address = "$C$2" Or "$C$3" Then", however, does not work.

My question:   If I wanted the drop-down to work for, e.g., 50 rows, how can I change the code?   That is, "If Target.Address = "$C$2:$C$51" Then" does **not** work.

Thank you in advance.
EEH





Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue 

Open in new window

0
On Demand Webinar - Networking for the Cloud Era
LVL 8
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

I would like to add another condition to the current formula in Column C, which is based on the entry in Col B. The current formula is:

=IFERROR(IF($B2="","",IF(RIGHT($B2,3)="YES","",TEXT(TODAY(),"dd mmm yyyy"))),"")

The format for Col B is always:
AA-DD MMM YYYY-NO
or
AA-DD MMM YYYY-YES

I would like to add the following condition to the above formula, based on the entry in Col E:

If Col E = "Cancelled" OR "Deferred", then populate Col C with date from Col E (formatted as above — TEXT(TODAY(),"dd mmm yyyy")

The format for Col E is always:
Cancelled-DD MMM YYYY
or
Deferred-DD MMM YYYY

Lastly, if both Col B and Col E have entries, then I would like the date from Col E to be entered in Col C, not the date from Col B.

I have attached a sample spreadsheet to show the results I am hoping for.

Thanks!
Andrea
EE_Sample_TwoConditions.xlsx
0
I have a exchange 2010, hybrid to exo 2013/office 365.  

I created an alternate authoritative domain in Office 365 only, not on-prem in 2010.

I'm trying to create a shared mailbox in exchange online but am not having much luck getting it to work, granting full rights.

I ran through this process to convert to a mailbox, then created and on prem object, change the exchangeGUID and did a sync.  It did not sync with Azure however, or read "Synced with Active Directory." in step 5.

Any idea's on this?  Do I need to create the domain and/or OU structure on prem for this to work?
0
I have an excel sheet with 6 different room types in a hotel. These room different items and quantities of those items in each room. One room may have 2 lamps, where as the other may have 1. I need to insert the aggregate data into 1 table in order to make an accurate budget of all the items that I will order.
Hotel-1.xlsx
0
Hello,

I have a macro that allows for new sheets to be created based off the amount of rows in the "Work Item" column in the posted excel database. Right now the "work item" updates into the worksheets created from the "template" spreadsheet, but the "description of work item" does not update into the "description job"  column in the templates, and I would like it to. Could someone please help me with this? Thanks!
0
In Excel 2007, how do I change for only one cell from dot to comma as separator for decimal value?

I want to keep the global language setting to English and keep global dot as decimal separator, and only change for this cell to a comma as decimal separator. Is that possible?
0
Thanks to Shums solution to my last question, I have now moved forward to the final question. The attached worksheet has a routine to add a new line on the first line where there is no value in column B, after B15. This is done with the Add a Line button. There is also an unused button now titled "Compound the Interest."

This routine needs to add a line using the same date in column B as the previous line AND copy the contents of the previous line columns C, F, G, H, I, J and K to the new line AND copy the amount of the previous line's column I to the new lines Column D and Column J.
Bill_Golden_NOTE-PAYABLE_v3.xlsm
0

Microsoft Office

58K

Solutions

124

Articles & Videos

39K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.