User Profile
Haytham Amairah
Silver Contributor
Joined 9 years ago
User Widgets
Recent Discussions
Re: Converting time to a digital number
RachelHoff Hi, The result of the formula is correct, but it appears this way because of cell formatting (Time format). You need to switch to General or Number format like the below screenshot: Also, you can apply the format in the formula as follows: =TEXT((A1-INT(A1))*24,"0.00") I'd like the mention that the formula can be simplified like this: =TEXT(A1*24,"0.00") Hope that helpsRe: User Defined Function not showing with up "="
Hi Katrina, I'm sorry about that! I forgot that this only works in the subroutines (Subs), not in the functions! With regards to the functions, you need to create a new workbook and save the function in a module in this workbook, then you have to save this workbook as Excel Add-in (*.xlam) file extension. After that, you need to copy this workbook into the XLSTART folder. To locate this folder, please follow this path: C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART But please note that the code you attached seems to be wrong! It's Option Explicit (Option without s). Also, you have to remove the period (.) after the End Function! Hope that helpsRe: View to show which cells are locked/unlocked
Hi Jon, You can set a conditional formatting rule and apply it to all worksheet to visualize whether a cell is locked or unlocked so that the unlocked cell can be highlighted in a specific format. To do that: Select the triangle in the left-hand corner of the worksheet Go to Home >> Styles >> Conditional Formatting >> New Rule In the New Formatting Rule dialog box, select Use a formula to determine which cells to format Insert this formula into the formula box: =CELL("protect",A1)=0 Press the Format button to choose the format Hit OK Hope that helpsRe: Unwanted automatic change of text number format to scientific
I suggest to use Text to Column instead as the below steps: Select the numbers Go to Data >> Data Tools >> Text to Columns Click Next button twice to reach out to the Step 3 of 3 In Step 3, select Text, and select a destination cell Click FinishRe: How to add a 'Go to Top of Document' link to each page of a Word doc
Alan Kelly Hi, You can add a link to go to the top of the document as follows: The link is clickable, but you need to click Ctrl+Left Mouse to follow it. When you hover the mouse over the link, you can see that as the below screenshot: If you've added the link in the Footer area, you need to activate the Header/Footer mode to be able to click the link. Hope that helpsRe: Convert image url to actual image in excel
Hi, After you run the code, these pictures get saved locally in the workbook. If you want to extract them into a folder outside Excel, the trick is to save the workbook as a Webpage (*htm; *html) using the Save As dialog box. After then, go to the webpage folder and find them inside it. Regards AlevizosRe: Wrong format when adding a new row into a "formatted excel table"
Hi Paul, This issue may occur if you format a range of cells (that has an existing format) as a table in a certain color scheme. You need to clear the old format by selecting the table, then go to Home >> Font >> Fill Color >> No Fill as shown in the below screenshot. I have answered a similar question before in this link. Hope that helps PaulSTOFFAESRe: Convert image url to actual image in excel
You can update the code like the following by adding this line to it: Pshp.Placement = xlMoveAndSize Sub URLPictureInsert() 'Updateby Extendoffice 20161116 'Update #1 by Haytham Amairah in 20180104 'Update #2 by Haytham Amairah in 20180108 Dim Pshp As Shape Dim xRg As Range Dim xCol As Long On Error Resume Next Application.ScreenUpdating = False Set Rng = ActiveSheet.Range("A2:A140") For Each cell In Rng filenam = cell ActiveSheet.Pictures.Insert(filenam).Select Set Pshp = Selection.ShapeRange.Item(1) Pshp.Placement = xlMoveAndSize If Pshp Is Nothing Then GoTo lab xCol = cell.Column + 1 Set xRg = Cells(cell.Row, xCol) With Pshp .LockAspectRatio = msoFalse .Width = 60 .Height = 30 .Top = xRg.Top + (xRg.Height - .Height) / 2 .Left = xRg.Left + (xRg.Width - .Width) / 2 End With lab: Set Pshp = Nothing Range("A2").Select Next Application.ScreenUpdating = True End Sub This will change the property for each image after inserting it to make it move and size with the cell.Re: Cell Range Delimiter
Hi Bob, I never knew about this trick before, but it's a really cool trick! I am using Excel 2019, and I can use it. You can select the starting cell, then type the period (.) to allow you to expand the range using just one arrow! And after entering the formula, the period (.) becomes colon (:). It seems that this feature has been broken or overwritten in Excel 365 because of the new data types in Excel (Stocks and geography) which comes with the new error type #FIELD!. I think we need some clarification from the Excel team. John Campbell JoeMcDaid
