Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Microsoft Office

60K

Solutions

40K

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

My VBA editor menus are grayed out.  The menus don't respond to mouse clicks or Alt key combinations.  I have tried uninstalling and reinstalling MS Office twice and no change.
0
Free Tool: Port Scanner
LVL 10
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

This has to be some simple setting, however, I have googled and googled to no avail. After I enter data into a cell and hit return, Excel underlines the entire row. This is not a new installation, just started doing this yesterday.
underline.docx
0
Hi I have an Excel 2010 .xlsx file vwhich I have forgotten the  password to.

Is  it possible to  either recover or circumvent it?
all I need is the contents of 1 sheet

I've seen other posts where you paste a macro unsure how that could work as soon as the spreadsheet is opened a password is required

Before someone ask no I can't upload it
0
column D has phone numbers in the following format

              D
123.456.6789

I would like to have the phone numbers moved to column K with the following format

            K
(123) 456-6789
0
I need to figure out how to use a Dynamic Range with an offset to be used in a Forecast formula....
0
Is there a way to offset from a dynamic named range to use the same number of rows as the dynamic named range does?
Dynamic.xlsx
0
I had this question after viewing Excel 2010 disable a cell based on another cell.

I am using this to so that if one cell is populated then another is not updatable. Is it possible to have another change option in a worksheet?

Option Explicit

Const PW As String = "123"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
'The code will be triggered if the cell being changed is in column D, starting from Row10
If Target.Column = 4 And Target.Row > 9 Then

    ActiveSheet.Unprotect Password:=PW
    If Target <> "" Then
        Cells(Target.Row, "E").Locked = True
    Else
        Cells(Target.Row, "E").Locked = False
    End If
    ActiveSheet.Protect Password:=PW
End If
End Sub
0
How can I sum up a number of rows which contact text rather than numbers?  Essentially what I am looking for is a cell to contain an answer to =count the number of rows as I add new rows
0
I've built a pivot table that is being sent to multiple users via email. When the users receive the attachment, they should be able to press an update button I've created to update the pivot table.

When users hit the update button, they receive an error message stating "pivot table was saved without underlying data" .  I've selected Save Source Data with file AND Refresh Data When Opening The File in pivot table options. However neither option saves once the file is closed.

What can I add to the macro to override this issue?
0
When  using save as to PDF from Visio 2016 in Windows 10, (File > Save As > PDF), you will get black boxes or placeholders and unusable links in the PDF document - see attached.
Visio-to-PDF.PNG
0
Free Tool: Site Down Detector
LVL 10
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I have an excel spreadsheet that if something is selected in a dropdown in cell D10 then E11 will be disabled
0
I am sure this is a simple fix, but I cannot figure out how to get the scrolling window in the attached worksheet titled - Dashboard -  of the attached workbook to stop when it reaches the bottom of the referenced range.  Please see the attached screenshot also.

Thank you for your assistance!
Screenshot.docx
NCCU---Contracts-Inventory-Listing-.xlsm
0
I have recently created a quotation tool which opens up into outlook when the quote is built as a screenshot to send to a customer. I have tested it on my system and it works perfectly, yet I have sent it to a colleague who uses windows 10 (im on 8) and Office 2016 (im on 2013) and it does not work. It just says the location inst available. Please can someone help me with this. I have attached the excel document created.
Quotation-test.xlsm
0
I am working on a project which is Excel based but part of which links to another internal system, a Purchase Order creation platform.

I want to be able to open the PO Platform from the Excel sheet, ideally without going to the Intranet home page first.

Our Intranet home page has a button which links to the PO Platform and I can copy the link from it.

When I use that as a hyperlink in Excel it does not behave the same as when clicking it direct from the Intranet.

Using the link on the Intranet just takes you to the PO platform with single sign on authentication whereas using the hyperlink in Excel shows an error saying user hasn't logged off and then goes to the login window for the platform.  I have spoken to our IT department and apparently it is to do with multiple logins for the PO platform being disabled and we do not want to re-enable it; they have tried in the past and it caused other issues.

So, I am thinking I can maybe set a macro that runs when the hyperlink is clicked. The macro would navigate to the Intranet home screen and "click" the button. The button is stored within a table on the home screen and has a specific jpg as a symbol.

Can this be done?

Thanks
Rob Henson
0
eThe below code is meant to split row data that contains a special character "/" within the cell data for example "SEL/EHL" and populate single value into the row below by having row only for "SEL" and a duplicated row with the cell now with the "EHL" value and to remove the "/" from the metadata that was split.

However the code below for "&" works fine but the above fails to run and generates a run time 9 error message "Subscript out of range" message.

Can any of my peers advise why its happening on this "/" piece of code and not the "&" part of the code even though they are meant to be doing the same thing.

Option Explicit

Sub clean_pos_data()
    Dim rawData() As Variant
    Dim startRange As Range
    Dim v As Long
    Dim i As Long
    Dim j As Long
    Dim x As Long
    Dim cleanData() As Variant
    Dim splitField() As String
    
    With ws_PosSeq
        Set startRange = .Range("a3")
        rawData = startRange.Resize(.UsedRange.Rows.count - startRange.Row, .UsedRange.Columns.count).Value
    End With
    
    For i = 1 To UBound(rawData, 1)
        If InStr(1, rawData(i, 9), "&") > 0 Then
            splitField = Split(CStr(rawData(i, 9)), "&")
            For x = 0 To UBound(splitField)
                 v = v + 1
            Next x
        Else
            If InStr(1, rawData(i, 9), "&") > 0 Then
                splitField = Split(CStr(rawData(i, 9)), "&")
                For x = 0 To UBound(splitField)
                    v = v + 1
             

Open in new window

0
Hi!

I have created 7 templates that I would like to write a Macro for that opens them all in succession for me to manually insert attachments and send.  After trawling a few forums I have cobbled this together however I am now struggling to see what I am missing in order to make this work once assigned to a toolbar button:

Dim template As String

Sub OpenTemplate1()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template1.oft"
MakeItem
End Sub

Sub OpenTemplate2()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template2.oft"
MakeItem
End Sub

Sub OpenTemplate3()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template3.oft"
MakeItem
End Sub

Sub OpenTemplate4()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template4.oft"
MakeItem
End Sub

Sub OpenTemplate5()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template5.oft"
MakeItem
End Sub

Sub OpenTemplate6()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template6.oft"
MakeItem
End Sub

Sub OpenTemplate7()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template7.oft"
MakeItem
End Sub

Private Sub MakeItem()
Set newItem = Application.CreateItemFromTemplate(template)
newItem.Display
Set newItem = Nothing
End Sub
0
Dear Respectable Experts,

i need help below code attached function Extract a number from a string value and returns numbers as string i need that it returns number as value or number please help me if it is possible.

Thanks.

Option Explicit

Public Function ExtractNumbers(AValue As Variant) As String
 
  Dim Character As String
  Dim Index As Long
  Dim Result As String
  Dim Value As String
  
  Value = CStr(AValue)
  For Index = 1 To Len(Value)
    Character = Mid(Value, Index, 1)
    If IsNumeric(Character) Then
      Result = Result & Character
    End If
  Next Index

  ExtractNumbers = Result
 
End Function

Open in new window

0
EE,

I administrate a small office environment of 25 users.  
> We use Outlook as our email client (Versions 2003, 2007, 2010 and 2016).  
> We receive our emails from a outside ISP/Exchange vendor, so we do not have access to the Exchange server, other than receiving our emails.

Due to the nature of our business, we 'live and die' by our emails, it is the life blood of our business.
> One of my biggest head aches is that some of our uses allow their PST files to grow to the point that the PST files start to get corrupt, requiring Scanpst and other measures to repair it.

I am looking for a tool (Either free or fee-based) that will go out and query each user, find the user's PST file (Or I can direct it there) and generate reports so that I can keep tabs on the sizes of the PST files.
> Additionally (The "icing on the cake") having the ability to monitor the Deleted and Send folders would be REALLY nice...

I hope that I am not asking for too much... Anyone have any suggestions?
> Again, fee-based tools are a consideration.

Rojosho
0
Hi,

Is there any change that this settings works in other language's?
I've got a English version of Office.

The word "Attachment"  is working fine when clicking send It gives a message(see screenshot) , but there are times that I'm writing a mail in Dutch.
And the Dutch word of attachment "bijlage" isn't working. Is there any chance you can add words to this setting?

Could you please help me to achieve this?

Thanks in advance.

Nando
Outlook-setting.jpg
0
What does it mean to be "Always On"?
LVL 4
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

I have the Office 365 Business Premium version. How can I get Powerpivot onto my version of Excel. Yes, I have checked Add-ins. It's not there. Thanks
0
Hi Experts,

my KMS host is WIN2012R2 server.
VAMT is installed.
WIN2016 KMS key is installed but after some days the office installations are not licensed.
Office 2010 KMS key is installed but after some days the office installations are not licensed.

How can I troubleshoot this ?
0
Hi Experts,

If you will see under attached file, some data seems indented, but I have not used the indenting. Why is that?
Goals-Template.xlsm
0
IT just installed Microsoft Word 2016 on my computer, so now I have both Word 2013 and Word 2016.  When I double click on a DOCX from within Windows Explorer it launches 2016 even though I:

Used Control Panel->Default Programs->Associate a file type or protocol with a program:

I associated both DOC and DOCX files with "C:\Program Files (x86)\Microsoft Office\Office15\WINWORD.EXE" which is Word 2013.

Any ideas on how to set a previous version of Word as the default?

Thanks in advance,
0
I want to use VBA to populate the cells in a single column with the following formula.

=IF(ISNUMBER(SEARCH("501020",A2)),"x","Y")

where A2 is the starting location of the text that I am searching for "501020", and C2 is the starting location of the result (x or Y).  Each subsequent row would have different text in column A and the appropriate result in column C.  The number of rows in the worksheet will vary ranging up to 500 rows.

I want to duplicate the same results that I get if I copied the above formula in C2, and pasted it down to the last row containing data in column A.
0
Any idea how to loop through pivot tables applying conditional formatting instead of doing it manually as below.

     ActiveSheet.PivotTables("Test1").PivotSelect "", xData, True
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -11489280
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
      
         ActiveSheet.PivotTables("Test2").PivotSelect "", xData, True
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With 

Open in new window

0

Microsoft Office

60K

Solutions

40K

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.