VBA USERFORM SCREEN SIZE
I tried to fit the vba userform with laptop screen size.But it is not coming properly.Kindly Suggest
See also questions close to this topic
return number of unique dates on another sheet in the same workbook
I have a project for work where I am trying to calculate the total number of days an employee worked, with data from one sheet feeding the number calculated on the same sheet, or another sheet in the same workbook.
There are several rows of data for each work day, and so I am looking to calculate the total number of unique dates for each employee separately. If the data is isolated to one employee then I can use:
to calculate the number of work days. I have tried using several additional formulas to get the value I'm looking for, including:
=SUMPRODUCT((TEXT('Data'!$A$1:$A$100, "yyyymm")="201804")*('Data'!B$1:$B$100="John Doe"))
=SUMIFS(IF(FREQUENCY('Sono Detail'!$B:$B,'Sono Detail'!$B:$B)>0,1),'Sono Detail'!$E:$E,'(Test) Sono Report Card 1.0'!$B$3)
Neither of these worked.
Removing Data Labels with values of zero then reset - VBA
I have a code that removes the data label(s) from a customized pie chart if the value of the cell is 0%. However, since my code loops so that the data changes, i completely lose the label for that particular category so when a new set of data is added and the value is not zero the label does not appear any more. How do I do it so that when the value is 0 the data label is removed but when the value is anything but zero then it would reappear, essentially resetting the original set up of the chart so that all values/categories have data labels.
Sub ChartLoop() Range("D2").Select ActiveCell.Range("C1:E1").Select Dim myPDF As String Dim i As Long For counter = 2 To 21 Sheets("CF").Select Range("'CF'!$D$" & counter & ":$F$" & counter).Select 'numbers Selection.Copy Sheets("CF-Chart").Select Range("B1:B3").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True 'this is for removing the data labels Dim iPts As Integer Dim nPts As Integer Dim aVals As Variant Dim srs As Series ActiveSheet.ChartObjects("Chart 5").Activate For Each srs In ActiveChart.SeriesCollection With srs If .HasDataLabels Then nPts = .Points.Count aVals = .Values For iPts = 1 To nPts If aVals(iPts) = 0 Then .Points(iPts).HasDataLabel = False End If Next End If End With Next ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select myPDF = "\\stchsfs\arboari$\Profile-Data\Desktop\Export Trial1\c2-" & Sheets("CF").Range("C" & i + 2).Value2 & ".pdf" ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myPDF, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False i = i + 1 Next counter End Sub
The first chart is how my regular chart would look like. The second chart is where I would want to remove the data label with the value 0 but keep the data label with categories and value for the other ones with value >0.
Can't access an excel pivot table field through a string value in VBA
I am trying to access a row field in an Excel Spreadsheet through a VBA Macro
Sub EnableSelectionSelPF() 'pivot table tutorial by contextures.com 'This is the Excel Module that will enable 'the selection for a single piviot table 'column on the first table of the sheet Dim PT As PivotTable Dim PF As PivotField Dim Column As String Column = "Service Address" On Error Resume Next Set PT = ActiveSheet.PivotTables(1) Set PF = PT.RowFields(Column) PF.EnableItemSelection = True End Sub
When replacing the column string variable with a numerical index value I'm able to access the desired field, however trying to use the string variable,
Column, or a string value as the Index yields no results for me.
I've checked to make sure that
PT.RowFields()could take string values so I'm not quite sure what the problem is.
Base Code Acquired From: Contextures