VBA excel macro for a button that selects named reference from a named range

See also questions close to this topic

• There is any way to put a Track bar in a VBA form?

  new guy at stackoverflow here.

  I would know if is possible to put a track bar in a VBA form, i am using this for a excel worksheet and with excel 2013 there is not such control. There is anything that i could do?

  sorry about my english, it is not my native language, feel free to correct me , i will appreciate!

  ty!
• Needed: formula to return list of rows in excel

  Say I have 3 boxes as follow:

  Box 1 contains: item 1 + item 2 + item 3

  Box 2 contains: item 2 + item 4 + item 5

  Box 3 contains: item 3 + item 6 + item 7

  I have them all in o e sheet. Is there is a formula in excel that can enter in a different sheet allow me to type just: “Box 1” and it automatically lists the contents on that particular box (each item in a different cell not all in one cell)?

  So I want to enter one word in a cell, that that will result a list of other cells all together. Hope that my question is clear.

  Thanks for your help.
• Excel ComboBox displays Data Validation List with repeated entries and large gaps

  I have created an Excel sheet, whereby when one double-clicks on a cell linked to a data validation entry, a combo-box appears in its place. The combo-box enables auto-searching of the entries in the data-validation list (which is a dynamic list of entries). However, on searching and hence re-sizing the dynamic list, members of this list are displayed multiple times within the dropdown and large gaps/spaces are also displayed.

  A link to the sheet is provided here: https://www.dropbox.com/s/mig2l8vx301yekt/night_template_names.xlsm?dl=0

  I would be grateful if anyone could provide some insight into why this is happening.

  Thanks
• VBA Excel Macro to automatically fill a matrix of Prouty

  I am a beginner in VBA. I've been stuck on my project for days, I have to fill two Prouty matrices automatically, that's why I really need your help.

  For my project, I must be able to display the risk identifier (IdRisk) of all the risks in a matrix of Prouty (4/96, this may seem odd but my teacher requires that the matrix be in this form: y-axis for Gravity and x-axis for Probability x Criticality and in my case, greatest Probability Value I have is equal to 96).

  My excel file contains the following:

  For each risk: line in "Table3" from my main tab "Analyse" is provided an IdRisk in order to find it easily. For each risk, in addition to the product Probability x Severity, there is a position in the Prouty matrix and a position in the residual Prouty matrix.

  I would like to be able, in my matrix of "Prouty" and my matrix of "Prouty Residual", to display in each cell the reference or references of risks which come from my "Table3".

  My macro can: - Empty the 2 matrices. - Indicates the number of rows in "Table3" & indicates the number of rows to display according to the "Yes" criterion in column A of "Table3" in a popup. - Fills the two matrices (Prouty (Column K of "Table3") and Residual Prouty (Column U of "Table3") with IdRisk (Column B of "Table3) of each risk for which it has been indicated" Yes "in (Column A of "Table3").

  What I miss is that the macro can fill me both matrices correctly, even if it does now, the result is distorted, because the algorithm is only suitable when this matrix is ​​square, which is unfortunately not my case.

  For those who can help me out of this problem, here is the code of my VBA Excel Macro :

  Sub Prouty()
  Dim lastligne As Integer, nbrisque As Integer, nbrisqueaffichés As Integer, i As Integer, j As Integer, k As Integer
  With Sheets("Analyse ")
      'Dernière ligne des risques
      lastligne = .Range("A8").End(xlDown).Row
      'Nombre de risques
      nbrisque = lastligne - 8
      'Nombre de risques a afficher
      nbrisqueaffichés = Application.WorksheetFunction.CountIf(.Range("A9:A" & lastligne), "=" & "Oui")
      MsgBox ("Nbr risques : " & nbrisque & " Nbr risques affichés : " & nbrisqueaffichés)
      'Vider les proutys
      Sheets("Prouty").Range("B5:CS8").ClearContents
      Sheets("Prouty Residuel").Range("B5:CS8").ClearContents
      'Remplissage de prouty
      For i = 9 To lastligne
          For j = 1 To 96
              For k = 4 To 1 Step -1
                  If .Range("A" & i) = "Oui" Then
                      If .Range("N" & i) = Sheets("Prouty").Cells(9, j + 1) And .Range("I" & i) = Sheets("Prouty").Cells(k + 4, 1) Then
                          Sheets("Prouty").Cells(k + 4, j + 1) = Sheets("Prouty").Cells(k + 4, j + 1) & " " & .Range("B" & i)
                      End If
                  End If
              Next k
          Next j
      Next i
      'Remplissage de prouty résiduel
      For i = 9 To lastligne
          For j = 1 To 96
              For k = 4 To 1 Step -1
                  If .Range("A" & i) = "Oui" Then
                      If .Range("AA" & i) = Sheets("Prouty Residuel").Cells(9, j + 1) And .Range("S" & i) = Sheets("Prouty Residuel").Cells(k + 4, 1) Then
                          Sheets("Prouty Residuel").Cells(k + 4, j + 1) = Sheets("Prouty Residuel").Cells(k + 4, j + 1) & " " & .Range("B" & i)
                      End If
                  End If
              Next k
          Next j
      Next i
  End With End Sub
  

  Thank you very much and in advance for your help.
• VBA - User Selection (workbook and sheet name) then Copy and Paste

  Is there a way to make it where user selects which sheet to copy from, after they opened file?

  I'd like to do this because there may be multiple sheets with same format, but have different names.

  Example: Original workbook named VSC (Contains sheets Compare, Plot)

  Secondary workbook named SF (Contains sheets Results1, Results2, Results3)

  User clicks button on VSC, and file dialog opens, and user selects SF in a certain directory, then asks user to select which sheet to choose from - user chooses Results2 sheet, copies the data (Range"B2:B5"), then pastes it back to Compare sheet.

  Would this be possible? I am not sure how to start.

  Current code that asks user to select SF workbook:

  Sub GetFilePath()
  Dim objFSO as New FileSystemObject
  
  Set myFile = Application.FileDialog(msoFileDialogOpen)
  With myFile
      .Title = "Choose File"
      .AllowMultiSelect = False
  If .Show <> -1 Then
      Exit Sub
  End If
  FileSelected = .SelectedItems(1)
  End With
  
• Plot a histogram using the int value when count() is giving me strings

  In this other question, the result is coming in a good way, but not good enough. The bins are strings, and the frequency plot is not real. It is like every item is a string, but I want them to be int. How can I do that?

  What I want is to know how big is, having thousands of rows with a 'date' for each action, the normal volume of entries for a normal day. This is my code:

  df["dayOfYear"] = df["date"].dt.dayofyear
  plt.hist(df.groupby(df["dayOfYear"]).count())
  

  It gives me the answer, but bins are strings, not int, so the X axis is a collection of strings, avoiding me of getting a nice gaussian distribution.

  I tried to int() it, but it doesn't work for a pandas.

  Any help? Thank you! Miguel.
• I want to get yesterday's date time in camel route header

  <setHeader headerName="order_creation_date">
      <simple resultType="java.lang.String">
          ${date-with-timezone:now:GMT-5:MM-dd-yyyy HH:mm:ss}
      </simple>
  </setHeader>
  

  the above code sets the current date with new york time zone in exchange header. I need 24 hours back date-time from current date in exchange header from camel XML
• Extract data for past 7 days (Cannot use current_date function)

  I am currently using Amazon Red Shift and I am trying to extract data for the past 7 days but do not want to use current_date function.

  Is there any other alternative way, any suggestions. Thanks..