Reading a bit online it seems OWC changed to Excel Services. Your best option is something like the below I assume.
http://spreadsheetpage.com/ind…ng_a_chart_in_a_userform/
https://www.homeandlearn.org/vba_charts_and_user_forms.html
Posts by chirayuw
-
-
I didn't understand, Are you saying if the date that they click on is Weekend then a message needs to show? If that's the case then add this into code for sheet "VerlofKalender Sjabloon"
Code
Display MorePrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Val As String Val = Application.WorksheetFunction.Weekday(ActiveCell, 1) If IsDate(ActiveCell) And Not IsEmpty(ActiveCell) And Val = 1 Then MsgBox "You have selected a weekend", vbCritical, "" ElseIf IsDate(ActiveCell) And Not IsEmpty(ActiveCell) And Val = 7 Then MsgBox "You have selected a weekend", vbCritical, "" Else End If End Sub -
you don't need a macro for this/ formula file attached
[ATTACH]n1200669[/ATTACH]
-
Data Validation lists are basically just cells from where you can select your value. So the way to change the font size is the same way as you would normally from the home tab in Excel. So you can either record a macro & modify it or use below code sample & modify it
CodeSheets("Sheet1").Range("A1").Font.Size = "11" -
sample file please
-
sample attached
[ATTACH]n1200664[/ATTACH]
-
theres 2 ways to do it. either format cell as text & enter date as text. or change number format of cell to dd-mm-yy. both examples below
Code
Display MoreSub TextFormatDate() Range("A1").NumberFormat = "@" Range("A1") = Format(Date, "dd-mm-yy") End Sub Sub NumberFormatDate() Range("A1").NumberFormat = "dd-mm-yy" Range("A1") = Date End Sub -
looking at your code - you haven't told the macro what wsName value is. So the macro doesn't seem to know the workbook name that you're referring to in which you have your worksheet
-
try this
Code
Display MorePrivate Sub UserForm_Initialize() Dim StDt As String Dim EndDt As String Dim StVal As Integer Dim EndVal As Integer Dim OVal As Integer StDt = CLng(DateSerial(Year(Date), 2, 1)) EndDt = CLng(Application.WorksheetFunction.EoMonth(StDt, 0)) StVal = Application.WorksheetFunction.CountIf(Range("A:A"), "<" & StDt) EndVal = Application.WorksheetFunction.CountIf(Range("A:A"), ">" & EndDt) OVal = Application.WorksheetFunction.CountA(Range("A:A")) - 1 'shows the count month is working MsgBox Month(Now) CFortyfive = OVal - (StVal + EndVal) TB_45Mnth.Value = CFortyfive End Sub -
Increase the range as in the range you copy which has the grouping e.g. lets say you grouped rows 1:10, then you may need to copy rows 1:11
-
You'll need to use IF with SUMIF to get what you want. See attached sample file. [ATTACH]n1200215[/ATTACH]
-
The "xlPasteFormats" part is what applies the formatting so it should take care of grouping as well. You may need to add it wherever you want the group to be applied. Also if group isn't applied then you may need to increase your range
-
try this
Code
Display MoreSub rangecopy() If Worksheets("Sheet1").Range("A7") = "ABC" Then Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0) = Worksheets("Sheet1").Range("I35").Value ElseIf Worksheets("Sheet1").Range("A7") = "XYZ" Then Worksheets("Sheet2").Range("B65536").End(xlUp).Offset(1, 0) = Worksheets("Sheet1").Range("I35").Value End If End Sub -
add the year argument since you're doing countifs
CodeCFortyfive = WorksheetFunction.CountIfs(Sheets("Archive").Range("A3:A10000"),Month(Now),Sheets("Archive").Range("A3:A10000"),Year(Now)) -
To save the address and in a cell & then reuse it lets use A1 as our cell that will hold the address & then take the address from cell value. Test it by opening a blank sheet, click on a random cell somewhere then run the macro line by line to see what it does
Code
Display MoreSub MyAddress() 'This bit puts the Activecell address in A1 Range("A1") = ActiveCell.Address 'This bit will copy the cell address as variable from A1 to use for Next macro Dim MyAdd As String MyAdd = Range("A1").Value End Sub -
So are you trying to figure out number of hours worked instead of days?
-
I have 1 simple question. Are you using a userform or not? If you're using a userform then the cell address can be saved as a global variable and then referenced to when next is run. Otherwise if you're just using them as normal macros then you'll need to first store the cell address somewhere on the sheet in a given cell, then reference to the cell value to get your address back when next is run
-
Test this. It copies all data & then does remove duplicates for columns A to H in DeliveryProb. Based only on using Columns A to F for remove duplicates.
Code
Display MoreSub Problem_Solver() Dim CurrRow As Integer 'Copy row Dim PstRow As Integer 'Paste row Dim MultiP As Integer 'Total Problem count Dim SingleP As Integer 'Single Problem loop Worksheets("Data Entry").Select Range("A3").Select Do Until IsEmpty(ActiveCell) CurrRow = ActiveCell.Row MultiP = Application.WorksheetFunction.CountA(Range("K" & CurrRow & ":O" & CurrRow)) If MultiP > 0 Then 'copy data For SingleP = 1 To MultiP 'Date Worksheets("Data Entry").Select Range("A" & CurrRow).Copy Worksheets("DeliveryProb").Select PstRow = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row Range("A" & PstRow).PasteSpecial xlPasteValues Range("A" & PstRow).NumberFormat = "dd/mm/yyyy" Application.CutCopyMode = False 'Driver Code Worksheets("Data Entry").Select Range("B" & CurrRow).Copy Worksheets("DeliveryProb").Select Range("B" & PstRow).PasteSpecial xlPasteValues Application.CutCopyMode = False 'Licence Plate Worksheets("Data Entry").Select Range("E" & CurrRow).Copy Worksheets("DeliveryProb").Select Range("D" & PstRow).PasteSpecial xlPasteValues Application.CutCopyMode = False Next SingleP Worksheets("Data Entry").Select 'K PstRow = Worksheets("DeliveryProb").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row If Not IsEmpty(Range("K" & CurrRow)) Then Worksheets("DeliveryProb").Range("E" & PstRow) = 1 'L PstRow = Worksheets("DeliveryProb").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row If Not IsEmpty(Range("L" & CurrRow)) Then Worksheets("DeliveryProb").Range("E" & PstRow) = 2 'M PstRow = Worksheets("DeliveryProb").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row If Not IsEmpty(Range("M" & CurrRow)) Then Worksheets("DeliveryProb").Range("E" & PstRow) = 3 'N PstRow = Worksheets("DeliveryProb").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row If Not IsEmpty(Range("N" & CurrRow)) Then Worksheets("DeliveryProb").Range("E" & PstRow) = 4 'O PstRow = Worksheets("DeliveryProb").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row If Not IsEmpty(Range("O" & CurrRow)) Then Worksheets("DeliveryProb").Range("E" & PstRow) = 6 Else End If ActiveCell.Offset(1, 0).Select Loop 'Finish Worksheets("DeliveryProb").Select PstRow = Worksheets("DeliveryProb").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row Range("C2:C" & PstRow).Formula = "=VLOOKUP(B2,DriverVal,2,FALSE)" Range("F2:F" & PstRow).Formula = "=VLOOKUP(E2,ProblemCode,2,FALSE)" Range("A:H").RemoveDuplicates Array(1, 2, 3, 4, 5, 6), xlYes MsgBox "Done", vbInformation, "" End Sub -
Refer to the Code Tags section of Rules of Posting in below link to get the code In a neat manner. As for your error I cant say what's causing it till I can test a sample file. I don't know how big your file is or what kind of data it uses in column A.
Try changing the "MaxID as Integer" to "MaxID as Long"
-
Try =OR(UPPER(F8)="C",F8="O")