Closing all hidden excel workbook
I am working with some excel workbook. Some of the workbooks are hidden. The hidden workbooks name are not same all time. They changes all the time. I want to run a macro which will close all hidden workbooks but do nothing with those workbook which are open but not hidden.
See also questions close to this topic
How to use IF statement inside a built in function
im trying to create a VBA built in function where if depending on the requirement, one type of calculation is done. this is what i tried to do but it is not working. anyone has an example of how do this type of function?
Public Function FRPV(ratey As Double, ratet0 As Double, maturity As Date, asof As Date, amount As Double, testif As Double) If testif = 123 Then FRPV = ((1 + (ratey / 100) * (maturity - asof) / 360) * amount) / (1 + (ratet0 / 100) * (maturity - asof) / 360) Else FRNPV = (((1 + ratey / 200) ^ ((maturity - asof) / 180)) * amount) / (1 + (ratet0 / 100) * (maturity - asof) / 360) End If End Function
Some cells in formatted date column appearing in mm-dd-yyyy while rest are dd-mm-yyyy
I have a file where I need to check if the current week falls within the specified dates for each record. The dates, named Effective and Expiry dates are in columns E and F. Since the file is downloaded from some database, I have done some level of cleaning on the columns. (Note: "current" week is always the previous week)
This is my code:
Public Sub datechk() Dim wb As Workbook Dim ws As Worksheet Dim i, cnt As Long Dim temp As String Dim current_Sun, current_Mon As Date Set wb = ThisWorkbook Set ws = wb.ActiveSheet current_Mon = Date - Weekday(Date, vbMonday) + 1 - 7 current_Sun = Date - Weekday(Date, vbSunday) + 1 ws.Range("C1").CurrentRegion.Replace What:="null", Replacement:="", _ SearchOrder:=xlByColumns, MatchCase:=False cnt = Application.WorksheetFunction.CountA(ws.Range("C:C")) 'short date format For i = 2 To cnt temp = ws.Cells(i, "E").Value On Error Resume Next ws.Cells(i, "E").Value = Mid(temp, 1, InStr(temp, " ") - 1) temp = ws.Cells(i, "F").Value On Error Resume Next ws.Cells(i, "F").Value = Mid(temp, 1, InStr(temp, " ") - 1) Next On Error GoTo 0 ws.Range("E:F").NumberFormat = "dd/mm/yyyy" For i = 2 To cnt If current_Sun <= ws.Cells(i, "E").Value Or current_Mon >= ws.Cells(i, "F").Value Then ws.Cells(i, "G").Value = 0 Else ws.Cells(i, "G").Value = 1 End If If IsEmpty(ws.Cells(i, "F")) Or ws.Cells(i, "F").Value = 0 Or ws.Cells(i, "F").Value = "" Then ws.Cells(i, "G").Value = 1 End If Next End Sub
Columns E and F contain the effective and expiry dates. Column G is my checker for whether my current week falls within the date range.
Now this works perfectly fine on my laptop. However, when my client ran the code, he was getting an error. Certain cells in the expiry date(F) column was appearing in (mm-dd-yyyy) format and hence throwing off the checker.
Eg: Where the expiry date was 01-10-2020, in my client's output file, it was showing as 10-01-2020.
This was not happening in every row and this was not happening with effective date(E). I have checked the output file my client has and the formatting on that particular cell is still dd-mm-yyyy.
Would someone happen to know why this is happening and how it could be fixed? Since I am not getting this error when I run the code myself, I am unable to try and fix it too.
(This is my first time posting. Please let me know if more info is needed)
Delete comments in the selected area
In Excel Online, we can select a range of the cells, right-click on it, and delete the comments that are located in this area. Is it possible to reproduce the same behavior using excel js API? If we apply
context.workbook.comments.getItemByCellto a cell that has no comments, an error occurs. If we try to add a comment to a cell that already has a comment, an error occurs too.
How can we find out if there is a comment in a cell using excel js API? Do you plan to add the
getItemByCellOrNullObjectmethod for handling comments or similar methods to help avoid these inconveniences with errors?
Updating a combobox from another form vba Excel
I have a Userform1 form, inside which there is a combobox called categories, and this field is a list of options that I load into the combobox from a table in excel (using rowsource in the object properties).
From the opened Userform1, if I want to add a new option to the list that appears in the categories field, I put a button that opens a Userform2 where I can create categories. But once I create it and close Userform2 the combobox of Userform1 is not updated. How can I refresh it so that the new option I created from Userform 2 appears?
Thank you very much
How to hide a common piece of text from a website
So for example on something like Redit, if I wanted to hide the "give award" text which shows on every post how would I be able to hide it from appearing multiple times?
What the text part I want to block looks like
Excel VBA - Select and Hide Entire Rows In-Between 2 Named Rows
I have two named rows with information in between that I would like to toggle between hidden and unhidden. I don't want to use Data Grouping because the information in between the named rows is moved around frequently.
The upper row YEAR2019 and YEAR2020 are to remain visible at all times.
I'm currently trying this but receive errors and can't seem to get it to work -
Sub HideRowsYEAR2020() Range(.Cells(.Range("YEAR2019").Row + 1), _ .Cells(.Range("YEAR2020").Row - 1)).Select If Selection.EntireRow.Hidden Then Selection.EntireRow.Hidden = False Else Selection.EntireRow.Hidden = True End If
Show, edit and update hidden records in FrontEnd (TYPO3)
I am building an extension (Classified Ads). The FrontEnd user has the option to set his Ad to hidden or the Ad gets the hidden status after some time (Ad expired). I faced two problems:
- if i try to call it on the
showAction()then TYPO3 gives back an error saying that the Object hasn't been found, which makes sense since there are restrictions.
- In the list action the URL to the show action won't render since the
persistedAliasMapperaspect sents a request including the same restrictions.
How do i get these problems solved so the user can edit his Ad?
- TYPO3: 10
- MODE: composer
- PHP: 7.4
- if i try to call it on the