+ Reply to Thread
Results 1 to 7 of 7

userform default option button

Hybrid View

  1. 07-15-2016, 10:51 PM #1
    adamheon
    adamheon is offline
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    userform default option button

    when i open this userform the option buttons default to "no"....when i click the ADD PROJECT button my the code does not recognize that the option buttons are selected on "no"...unless i toggle them prior to pushing ADD PROJECT.

    here is the add project code: (sample form attached)

    Private Sub cmdAdd_Click()
Dim LRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("PROJECTS")

'find first empty row in database
LRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

lPart = Me.cmbTYPE.ListIndex

'check for a part number
If Trim(Me.jobno.Value) = "" Then
  Me.jobno.SetFocus
  MsgBox "ENTER JOB NUMBER"
  Exit Sub
End If

Dim JobNum As Range
Set JobNum = ws.Range("C1:C" & LRow).Find(What:=Me.jobno, SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues)

If JobNum Is Nothing Then
    'rest of code to push to sheet.
Else
    MsgBox "JOB NUMBER ALREADY ENTERED"
    
Exit Sub
End If

'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
  .Cells(LRow, 2).Value = Me.cmbTYPE.Value
  .Cells(LRow, 3).Value = Me.jobno.Value
  .Cells(LRow, 4).Value = Me.jobname.Value
  .Cells(LRow, 5).Value = Me.state.Value
  .Cells(LRow, 6).Value = Me.town.Value
  .Cells(LRow, 7).Value = Me.dateentered.Value
  .Cells(LRow, 8).Value = Me.cmbPa.Value
  .Cells(LRow, 9).Value = Me.cmbPm.Value
  .Cells(LRow, 11).Value = Me.arcode.Value
  .Cells(LRow, 12).Value = Me.billto.Value
  .Cells(LRow, 13).Value = Me.contract.Value

  .Cells(LRow, 15).Value = Me.reqdue.Value
  .Cells(LRow, 16).Value = Me.cmbStatus.Value
  .Cells(LRow, 17).Value = Me.notes.Value
  .Cells(LRow, 32).Value = Me.billingnotes.Value
  
End With

'clear the data
Me.cmbTYPE.Value = ""
Me.jobno.Value = ""
Me.jobname.Value = ""
Me.state.Value = ""
Me.town.Value = ""
Me.cmbPa.Value = ""
Me.cmbPm.Value = ""
Me.arcode.Value = ""
Me.billto.Value = ""
Me.contract.Value = ""

Me.reqdue.Value = ""
Me.cmbStatus.Value = ""
Me.notes.Value = ""

Exit Sub

  
If ocipyes.Value = True Then
    ws.Cells(UpdateRow, 14).Value = "1"
Else
    ws.Cells(UpdateRow, 14).Value = "0"
End If

If budgetyes.Value = True Then
    ws.Cells(UpdateRow, 19).Value = "1"
Else
    ws.Cells(UpdateRow, 19).Value = "0"
End If

If sovyes.Value = True Then
    ws.Cells(UpdateRow, 20).Value = "1"
Else
    ws.Cells(UpdateRow, 20).Value = "0"
End If

If bondyes.Value = True Then
    ws.Cells(UpdateRow, 21).Value = "1"
Else
    ws.Cells(UpdateRow, 21).Value = "0"
End If

If estyes.Value = True Then
    ws.Cells(UpdateRow, 22).Value = "1"
Else
    ws.Cells(UpdateRow, 22).Value = "0"
End If

If paulyes.Value = True Then
    ws.Cells(UpdateRow, 23).Value = "1"
Else
    ws.Cells(UpdateRow, 23).Value = "0"
End If

If steveyes.Value = True Then
    ws.Cells(UpdateRow, 24).Value = "1"
Else
    ws.Cells(UpdateRow, 24).Value = "0"
End If

If pmyes.Value = True Then
    ws.Cells(UpdateRow, 25).Value = "1"
Else
    ws.Cells(UpdateRow, 25).Value = "0"
End If
    
    
End Sub
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-15-2016, 11:03 PM #2
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,359

    Re: userform default option button

    Hi adam,

    Look at the Properties of those options buttons and change the default VALUE = True instead of false.

    You need to show the properties window and click on the option button you want to change. Then look down the list of properties to Value and change that False to a True.

    I hope that is what you need. If not then keep asking.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  3. 07-15-2016, 11:48 PM #3
    adamheon
    adamheon is offline
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Re: userform default option button

    Hi MarvinP, i do have those option button values set to True. When the userform opens the option buttons are set to No like i need...but when i click the add button it should return the result of "0" into a specific cell....if i dont click on any of the option buttons and i click the add button it returns no value even those the option button is on.
    Reply With QuoteRegister To Reply
  4. 07-16-2016, 08:28 AM #4
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,359

    Re: userform default option button

    Hi adam,

    I guess I don't understand the problem. You can set properties using VBA and you can query them using VBA. Is it your code that isn't working or the default settings that are changing?
    Reply With QuoteRegister To Reply
  5. 07-16-2016, 09:16 AM #5
    adamheon
    adamheon is offline
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Re: userform default option button

    Yes the code seems to be the problem. The option buttons should return a value of 1 or 0 along with all the other date entered. but they only work when i use the update button...not the add button.

    so if you go into the sample...enter a job number and then click add...it should populate all the option buttons as "0" for that row.
    Reply With QuoteRegister To Reply
  6. 07-16-2016, 02:32 PM #6
    adamheon
    adamheon is offline
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Re: userform default option button

    so a solution i think is to change this:

    If ocipno.Value = True Then
    ws.Cells(UpdateRow, 14).Value = "0"
Else
    ws.Cells(UpdateRow, 14).Value = "1"
End If
    to just:

    .Cells(LRow, 14).Value = "0"
    that way a "0" just gets entered into the cell. so i made that change and now i'm getting a syntax error. with the End with ??

    Private Sub updatenow_Click()

Dim ws As Worksheet
Dim JoBRow As Range
Dim UpdateRow As Long

Set ws = Sheets("PROJECTS")
Set JoBRow = ws.Range("C:C").Find(What:=JobSearch, LookIn:=xlValues, lookat:=xlWhole)

    If JoBRow Is Nothing Then
        MsgBox "CANNOT FIND JOB NUMBER"
        Exit Sub
    Else
        UpdateRow = JoBRow.Row
    End If

    

ws.Cells(UpdateRow, 18).Value = tb1

ws.Cells(UpdateRow, 26).Value = tb2
ws.Cells(UpdateRow, 27).Value = tb3

Exit Sub

If budgetyes.Value = True Then
    ws.Cells(UpdateRow, 19).Value = "1"
Else
    ws.Cells(UpdateRow, 19).Value = "0"
End If

If sovyes.Value = True Then
    ws.Cells(UpdateRow, 20).Value = "1"
Else
    ws.Cells(UpdateRow, 20).Value = "0"
End If

If estyes.Value = True Then
    ws.Cells(UpdateRow, 22).Value = "1"
Else
    ws.Cells(UpdateRow, 22).Value = "0"
End If


If paulyes.Value = True Then
    ws.Cells(UpdateRow, 23).Value = "1"
Else
    ws.Cells(UpdateRow, 23).Value = "0"
End If


If steveyes.Value = True Then
    ws.Cells(UpdateRow, 24).Value = "1"
Else
    ws.Cells(UpdateRow, 24).Value = "0"
End If


If pmyes.Value = True Then
    ws.Cells(UpdateRow, 25).Value = "1"
Else
    ws.Cells(UpdateRow, 25).Value = "0"

End If

  'clear the data
Me.JobSearch.Value = ""
Me.cmbPm.Value = ""
Me.tb1.Value = ""
Me.tb2.Value = ""
Me.tb3.Value = ""

Exit Sub

End Sub
    Last edited by adamheon; 07-16-2016 at 02:52 PM.
    Reply With QuoteRegister To Reply
  7. 07-16-2016, 01:34 PM #7
    adamheon
    adamheon is offline
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Re: userform default option button

    I thought it might be becuase i have this part:

    End With

'clear the data
Me.cmbTYPE.Value = ""
Me.jobno.Value = ""
Me.jobname.Value = ""
Me.state.Value = ""
Me.town.Value = ""
Me.cmbPa.Value = ""
Me.cmbPm.Value = ""
Me.arcode.Value = ""
Me.billto.Value = ""
Me.contract.Value = ""

Me.reqdue.Value = ""
Me.cmbStatus.Value = ""
Me.notes.Value = ""

Exit Sub

    in front of this part:

      
If ocipno.Value = True Then
    ws.Cells(UpdateRow, 14).Value = "0"
Else
    ws.Cells(UpdateRow, 14).Value = "1"
End If

If budgetno.Value = True Then
    ws.Cells(UpdateRow, 19).Value = "0"
Else
    ws.Cells(UpdateRow, 19).Value = "1"
End If

If sovno.Value = True Then
    ws.Cells(UpdateRow, 20).Value = "0"
Else
    ws.Cells(UpdateRow, 20).Value = "1"
End If

If bondno.Value = True Then
    ws.Cells(UpdateRow, 21).Value = "0"
Else
    ws.Cells(UpdateRow, 21).Value = "1"
End If

If estno.Value = True Then
    ws.Cells(UpdateRow, 22).Value = "0"
Else
    ws.Cells(UpdateRow, 22).Value = "1"
End If

If paulno.Value = True Then
    ws.Cells(UpdateRow, 23).Value = "0"
Else
    ws.Cells(UpdateRow, 23).Value = "1"
End If

If steveno.Value = True Then
    ws.Cells(UpdateRow, 24).Value = "0"
Else
    ws.Cells(UpdateRow, 24).Value = "1"
End If

If pmyno.Value = True Then
    ws.Cells(UpdateRow, 25).Value = "0"
Else
    ws.Cells(UpdateRow, 25).Value = "1"
End If
    
End With
    but when i switch them, it's not clearing...and it still doesn't return any value. 

    Private Sub cmdAdd_Click()
Dim LRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("PROJECTS")

'find first empty row in database
LRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

lPart = Me.cmbTYPE.ListIndex

'check for a part number
If Trim(Me.jobno.Value) = "" Then
  Me.jobno.SetFocus
  MsgBox "ENTER JOB NUMBER"
  Exit Sub
End If

Dim JobNum As Range
Set JobNum = ws.Range("C1:C" & LRow).Find(What:=Me.jobno, SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues)

If JobNum Is Nothing Then
    'rest of code to push to sheet.
Else
    MsgBox "JOB NUMBER ALREADY ENTERED"
    
Exit Sub
End If

'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
  .Cells(LRow, 2).Value = Me.cmbTYPE.Value
  .Cells(LRow, 3).Value = Me.jobno.Value
  .Cells(LRow, 4).Value = Me.jobname.Value
  .Cells(LRow, 5).Value = Me.state.Value
  .Cells(LRow, 6).Value = Me.town.Value
  .Cells(LRow, 7).Value = Me.dateentered.Value
  .Cells(LRow, 8).Value = Me.cmbPa.Value
  .Cells(LRow, 9).Value = Me.cmbPm.Value
  .Cells(LRow, 11).Value = Me.arcode.Value
  .Cells(LRow, 12).Value = Me.billto.Value
  .Cells(LRow, 13).Value = Me.contract.Value

  .Cells(LRow, 15).Value = Me.reqdue.Value
  .Cells(LRow, 16).Value = Me.cmbStatus.Value
  .Cells(LRow, 17).Value = Me.notes.Value
  .Cells(LRow, 32).Value = Me.billingnotes.Value
  

Exit Sub

  
If ocipno.Value = True Then
    ws.Cells(UpdateRow, 14).Value = "0"
Else
    ws.Cells(UpdateRow, 14).Value = "1"
End If

If budgetno.Value = True Then
    ws.Cells(UpdateRow, 19).Value = "0"
Else
    ws.Cells(UpdateRow, 19).Value = "1"
End If

If sovno.Value = True Then
    ws.Cells(UpdateRow, 20).Value = "0"
Else
    ws.Cells(UpdateRow, 20).Value = "1"
End If

If bondno.Value = True Then
    ws.Cells(UpdateRow, 21).Value = "0"
Else
    ws.Cells(UpdateRow, 21).Value = "1"
End If

If estno.Value = True Then
    ws.Cells(UpdateRow, 22).Value = "0"
Else
    ws.Cells(UpdateRow, 22).Value = "1"
End If

If paulno.Value = True Then
    ws.Cells(UpdateRow, 23).Value = "0"
Else
    ws.Cells(UpdateRow, 23).Value = "1"
End If

If steveno.Value = True Then
    ws.Cells(UpdateRow, 24).Value = "0"
Else
    ws.Cells(UpdateRow, 24).Value = "1"
End If

If pmyno.Value = True Then
    ws.Cells(UpdateRow, 25).Value = "0"
Else
    ws.Cells(UpdateRow, 25).Value = "1"
End If
    
End With

'clear the data
Me.cmbTYPE.Value = ""
Me.jobno.Value = ""
Me.jobname.Value = ""
Me.state.Value = ""
Me.town.Value = ""
Me.cmbPa.Value = ""
Me.cmbPm.Value = ""
Me.arcode.Value = ""
Me.billto.Value = ""
Me.contract.Value = ""

Me.reqdue.Value = ""
Me.cmbStatus.Value = ""
Me.notes.Value = ""

Exit Sub
End Sub
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Userform: requiring fields and using a check box to set default option boxes
    By HeyInKy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-28-2014, 10:07 AM
  2. UserForm Option Button Help...
    By z-eighty2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-30-2013, 10:17 PM
  3. Userform option button help
    By mecharissa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-01-2012, 04:48 AM
  4. Userform Option Button to Show/Hide Another Option Button on same Userform
    By R_S_6 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-26-2010, 09:44 AM
  5. Making an option button default
    By peedarp1985 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2010, 02:31 AM
  6. Default Option Button Values
    By maxwell13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2008, 06:03 PM
  7. Option Button as default
    By StephanieH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2005, 10:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1